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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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