1

I have a machine running SQL 2014 but have neither the windows authentication or sa authentication details due to mismanagement of a previous support team.

Having tried logging in to SQL Management Studio using my Microsoft AD account I received the following error:

===Cannot connect to BACKUP-MGMT-01\CONSULT.=== A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (.Net SqlClient Data Provider)

I suspect this is due to my account not having privileges.

I then found some steps online allowing you to add a new sa or windows authenticated user via SQLCMD. However, when I run either the SQLCMD -S .\BACKUP-MGMT-01\CONSULT or SQLCMD -S .\BACKUP-MGMT-01 I just receive the following error:

C:\Users\frank>SQLCMD -S .\BACKUP-MGMT-01\CONSULT Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : SQL Server Network Inte rfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. . Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login timeout expired. Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : A network-related or in stance-specific error has occurred while establishing a connection to SQL Server . Server is not found or not accessible. Check if instance name is correct and i f SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

Can anybody assist?

Improve this question
6
  • Do you have access to an admin account of windows (member of the Administrators group) on the server? If yes, you could try Start SQL Server in Single-User Mode.
    – Ronaldo
    Commented May 12, 2020 at 11:09
  • Hi Ronaldo, thank you for the info. Does this mean I can login to the management studio without any credentials and create a new sa account?
    – Fraz Mahmud
    Commented May 12, 2020 at 12:09
  • Yes Fraz it's exactly that, but it will only work if the Remote DAC setting is turned on
    – Jhunter1
    Commented May 12, 2020 at 13:29
  • Yes. You can follow the instructions of the Connect to SQL Server When System Administrators Are Locked Out documentation.
    – Ronaldo
    Commented May 12, 2020 at 13:30
  • Hi Ronaldo, thanks for your help with this. The instructions in the link worked and I was able to login and add a new user for Windows authentication .
    – Fraz Mahmud
    Commented May 13, 2020 at 17:26

1 Answer 1

Reset to default
1

The correct format of the parameter -S for the SQLCMD prompt is: Server\Instance.

You seem to have tried localhost\instance\database which doesn't work.

SQLCMD -S .\BACKUP-MGMT-01\CONSULT 
          ^^        ^     ^   ^
          ||        |     |   +--- Database 
          ||        |     +------- Separator
          ||        +------------- Instance
          |+---------------------- Separator
          +----------------------- Server (. = localhost)

In any case the error message is stating you are having issues with the server name and/or instance name.

If your server is named BACKUP-MGMT-01 and your instance is named CONSULT then you might want to try:

SQLCMD -S BACKUP-MGMT-01\CONSULT -E

If your instance is really named BACKUP-MGMT-01 then you might want to try:

SQLCMD -S .\BACKUP-MGMT-01 -E -d CONSULT

Where -d is the parameter for database and -E is for trusted connections.

Reference Reading

sqlcmd Utility (Microsoft | Docs | SQL Docs)

Improve this answer
1
  • 1
    Awesome. Thanks for the detailed information - much appreciated. BTW, the windows server is called "BACKUP-MGMT-01" and the database instance is called "CONSULT". So i going by your comments above i should have tried: SQLCMD -S BACKUP-MGMT-01\CONSULT -E
    – Fraz Mahmud
    Commented May 13, 2020 at 17:24

Not the answer you're looking for? Browse other questions tagged or ask your own question.