3

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.

ctype.h
  • 205
  • 1
  • 3
  • 11
nacho10f
  • 135
  • 1
  • 4
  • Is your sql server express installed on the same machine as your management studio, or are you trying to connect from another machine? – Dave Holland Oct 16 '10 at 04:09

3 Answers3

9

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:

alt text

If you are having trouble setting up a new user account, just open up MS SQL Server Management Studio:

alt text

Open Security and right click on Login and create a new login:

alt text

setting the type to SQL Server authentication and adding a password (don't forget to uncheck the must change box):

alt text

You then just have to map the new user to the database:

alt text

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.

alt text

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).

alt text

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:

alt text

then go to the SQL Server Network Configuration:

alt text

then double click on the Protocols for MSSQLSERVER:

alt text

and make sure that TCP/IP is enabled:

alt text

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:

alt text

open the properties window:

alt text

and go to the connections tab and insure remote connections are allowed:

alt text

Larry Smithmier
  • 418
  • 1
  • 4
  • 7
  • 1
    Good post Larry - you only missed one possible piece - if he's trying to connect from a machine other than the SQL server he needs to Allow Remote Connections. – Dave Holland Oct 16 '10 at 04:14
  • Good point, I will add it. – Larry Smithmier Oct 16 '10 at 04:16
  • Thanks for your thorough answer... my problem seems to be before all of this then... first of all it's not a remote connection.. sql server express 2008 is installed on this machine.. second.. I cant get to any of these options cause as soon as i open management studio I get prompted to enter login and password and thats when I get the error of no server found – nacho10f Oct 16 '10 at 04:27
  • Hello NachoF. Ok, that changes things a bit. What OS are you running on? Is the prompt for password when you open MS or when you try to connect? What are you putting in for the server name (it should be localhost\SQLEXPRESS)? Screenshots (I use http://JingProject.com) would help. Let me get Express down, installed, and I will take a look. – Larry Smithmier Oct 16 '10 at 04:56
  • Let me know if that doesn't help and I will try again. – Larry Smithmier Oct 16 '10 at 05:22
  • that was it.. how was I supposed to know I do to put localhost\SQLEXPRESS... I was just trying localhost.... this answer was AWESOME! – nacho10f Oct 16 '10 at 05:42
0

1) Check the service is running - it might be under MSSQL, Microsoft SQL Server or SQL Server, I can't remember which.

2) Check the SQL Browser service is running - if not, start it. If it doesn't exist, that's OK.

3) The most common cause I find of this is the way SQL server deals with running more than once on a server. If you do an install once, it normally becomes the default database engine and you can connect to it as (SERVERNAME).

But if you install many database engines, then in MS SQL terms they are named (SERVERNAME)\instancename and if you try to connect to the wrong one then it wont accept your login.

So, if it is installed with an instance name (it might have prompted you during the install?), you will need to find out or put that name in - or use the dropdown for the Server Name and try "Browse" (might need SQL Browser) to see if it can tell you the right name, or you might find it in SQL Server Configuration Manager.

Also, MS SQL database engines normally run on port 1433, but if they don't then you either need the SQL Browser service running to direct the client to the right port, or you need to specify the port yourself, which you can find in the TCP/IP settings in Configuration Manager. I see your error is about Named Pipes connections which might need to be enabled first, also in Config Manager.

So,

Check services are running
Find database / instance name 
Check Names Pipes and TCP/IP are enabled in Config Manager
Check TCP/IP Port number
TessellatingHeckler
  • 5,726
  • 3
  • 26
  • 44
-1
In some cases this is not related to authentication.
In some case that you entered server address with any mistake , this error apears!


If you can put your code here to more help:D

user57205
  • 1
  • 3