I just installed SQL Server Express 2008 and Management Studio but when I try to connect I get
A network-related or
instance-specific error occurred while
establishing a connection to SQL
Server. The server was not found or
was not accessible. Verify that the
instance name is correct and that SQL
Server is configured to allow remote
connections. (provider: Named Pipes
Provider, error: 40 - Could not open a
connection to SQL Server) (Microsoft
SQL Server, Error: 2)
I want to use SQL Server authentication but I'm not sure how to set up accounts with username and passwords. Help, I'm new to this; I Have only had experience with MySQL and this seems much more complicated.
Best Answer
To connect to a local SQLExpress instance with Windows Authentication using Management Studio you will need to set the Server Name and Authentication as follows:
If you are having trouble setting up a new user account, just open up MS SQL Server Management Studio:
Open Security and right click on Login and create a new login:
setting the type to SQL Server authentication and adding a password (don't forget to uncheck the must change box):
You then just have to map the new user to the database:
The easiest way to test this new account is with Universal Data Link (.udl) files. No code required. Simply create an empty text file on a Windows machine with the extension .udl and double click it.
A wizard pops up and you can test your configuration pretty quickly (your instance name, the part to the right of the '\', will be SQLEXPRESS).
And, as a bonus, once you get it working you can open it in a text editor and extract your connection string. If it doesn't work on your local box, try it on the server itself. If that works, skip on down to the end of this for the culprit.
If none of that works, the problem you are probably having is related to the setup of SQL Server itself. Open up the SQL Server Configuration Manager:
then go to the SQL Server Network Configuration:
then double click on the Protocols for MSSQLSERVER:
and make sure that TCP/IP is enabled:
If TCP/IP is enabled, you may be running into firewall issues (it requires port 1433 by default, but it can be configured to a different port).
Edit: As Dave mentioned, you could also run into trouble if you are trying to connect remotely and it isn't allowed. To check/change this, Open up Management Studio and right click on the instance you are connected to:
open the properties window:
and go to the connections tab and insure remote connections are allowed: