Sunday, March 31, 2013

Allow SQL Server Authentication connection in SQL Server Management Studio Express 2012.

If you got this error message when attempting a SQL Server Authentication connection in lieu of a Windows Authentication connection in Mircosoft SQL Server Management Studio Express 2012 would you know what to do?

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.) (Microsoft SQL Server, Error: 233)

 
 

Well, I have spent my morning figuring this out and have fought my way through these four links:

  1. http://msdn.microsoft.com/en-us/library/cc646023.aspx
  2. http://www.bopup.com/support/install_sql_server_2005_2008_2012_as_database_server.html
  3. http://blogs.technet.com/b/danstolts/archive/2011/06/08/how-to-open-firewall-port-1433-for-sql-server-database-engine-for-use-with-scom-or-anything-else.aspx
  4. http://csharpdotnetfreak.blogspot.com/2010/01/sql-server-shared-memory-provider-error.html

 
 

The second half of the last article had my solution:

  1. Log onto SQL Server Management Studio Express 2012 as yourself with Windows Authentication.
  2. In the "Object Explorer" right-click on the server name and pick "Properties."
  3. The "Server Properties" dialog box will appear. Click on Security at the upper left.
  4. Change the "Server authentication" radio button from "Windows Authentication mode" to "SQL Server and Windows Authentication mode" and then click the "OK" button.
  5. Back at the "Object Explorer" you should again right-click the server and then this time pick "Restart."

 
 

Yay! While fucking around in trying to figure out how to do this I learned anew how to open up port 1433 at my firewall. This post I wrote some time back has details applicable to Windows 8 too, especially the part on Windows Firewall with Advanced Security, though I got to that dialog box on my laptop by going to the control panel, picking "Large Icons" from "View by:," clicking "Windows Firewall" and then clicking "Advanced settings."

No comments:

Post a Comment