Why Can't I Connect to my SQL Server Database - 1 - Using SQL Server Security

Sadly I cannot just give you the answer to this one.  You'll have to do some diagnosis yourself.  This is a basic guide to find out where the blockage lies.

1. What is SQL Server Security?

This is when you have a user id and password visible in the connection string.  So the connection string would look like...

Server=myServerName\myInstanceName;Database=myDataBase;User Id=myUsername;
Password=myPassword;

Notes: The server name may be specified as a single dot ('.') to mean 'same machine as I am connecting from'.  Instance name is only used if you have installed SQL server as a named instance.  Some do, some don't.  The default is no named instance for 'full' SQL Server, and sqlexpress for SQL Server express.

2. Can you connect from the server?

Install SQL Server Management Express (SSME) on the machine that hosts the database.  Can you connect from SSME using the id and password which you believe to be valid for the database.  If you can't then you need to find out why and fix it.

3. Can you connect from the client?

Install SQL Server Management Express on the machine (the client) that you want to connect from.  Can you connect to the remote server and database?  Use the id and the password that Step 2 proved would work.  If you can't then you know that there is a problem in the connectivity.  Things to try:

• can you ping the server from the client (this may not be conclusive as the server may have ping responses turned off)
• research the connection path between the client and the the server.  Do any firewalls or other network devices allow SQL server traffic to pass?  This is usually TCP port 1433 for normal queries and responses.  Whomever manages that SQL Server may have adjusted the port it listens on, but that is quite rare.  SQL Server also has other ports it uses for other purposes.
• does the server have a firewall?  Does it allow SQL server traffic in?
• does the server SQL instance allow remote connections?
• does it allow remote connections on the protocol stack you plan to use?  Usually TCP/IP.
• do you have the TCP/IP (or whatever you plan to use) support installed?

3. If you can pass all the checks above then I'm out of ideas for the moment.  Feel free to add a comment if you have any additional things to try.