0

I have been using SSMS and logging in through server authentication for a week now and yesterday when I tried to login it suddenly told me "no process on the other end of the pipe". I researched online and it said the problem is that the security property for the server is set to windows authentication and has to be changed to server authentication. So I log in to SSMS through windows authentication first to change it, however it said that I do not have permission to change the authentication mode, which is weird because I ran the SSMS as admin and it was on server authentication previously.

Now I can not access my database at all as when I created the database it was under the SA account and it will just tell me I do not have permission to open the database or do anything basically.

Also just for a side info the problem arised after I restarted my computer I believe, the server property is suddenly changed to windows authentication and I cannot log in through the SA account nor can I change it back to server authentication

Improve this question
1

2 Answers 2

Reset to default
0

The issue here is most likely that "Windows Authentication" was chosen (by yourself or someone else with access) but the service not restarted. Following your machine restart, SQL Server came up with only Windows Authentication enabled.

To regain access to your SQL instance, you need to restart the server in admin mode and re-enable access. This link from Microsoft describes the process, however, below is a high-level overview:

  1. Stop SQL Server service
  2. Add the -m switch as a startup flag
  3. Start SQL Server
  4. Connect (via SSMS or SQLCMD). If connecting via SSMS, make sure not to open Object Explorer as it will still the one admin connection allowed.
  5. Add your Windows login as a sysadmin
  6. Stop SQL and remove the -m startup flag
  7. Start SQL and login with Windows Auth.
Improve this answer
0

Check this pinal dave solutions - https://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/

Improve this answer
1
  • Please don't add link only answers. If the link rots the answer loses value. Can you include relevant parts so the answer has enough information by itself?
    – Tom V
    Apr 17, 2019 at 19:12

Your Answer

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

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