Connect to your SQL Server

0. Introduction: Terms

If you are already familiar with all the terms, you can skip this section.   Anyone else just read on for the quick introduction of the terms that are important to know and distinguish, when troubleshooting SQL server login issues.  First, the pure abstract definition as I would give them:


Some quick notes:

In the next step, let's see where/how to configure/check the different settings.

1. Configuration Details Screens

A SQL Server Instance can be a named instance or a default instance. You can check if the instance you have to connect to is named or not by using the Computer Management tool (on the server): The highlighted line shows a default instance, as the name in brackets is MSSQLSERVER. To be a named instance, it must be a different name, for example of SQL Server Express editions, it is usually SQLEXPRESS. To really see the difference, pop up the service details. A named instance has as internal name with a $ (dollar sign) in it, for example MSSQL$SQLEXPRESS.

If you can login to the instance, you can detect the version using either of those 2 small queries:

SELECT @@VERSION

Select all Open in new window

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

Select all Open in new window

The explanations are given in the following Microsoft support note: support.microsoft.com/kb/321185

The default port for MS SQL server is 1433 (for all versions I know of, aka version 7). For each instance, there MUST be a different port specified. The SQL Server Configuration tool (an MMC snap-in) can be called here:or you can set it directly in the computer management studio. I show the tcp/ip configuration details which shows the port:If you change the port, you must restart your SQL Server instance to make the change effective, and all clients need to be "informed" so they change their connection information.

In that same tool, you can also enable/disable all the protocols your server will listen on.  To confirm that the configuration is really active, check the SQL Server ERRORLOG file, it will list all the protocols, like this:For more details, I will refer you to the following MSDN links:
    SQL 2008: http://msdn.microsoft.com/en-us/library/ms174212.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/library/ms177440.aspx
    SQL 2000: http://support.microsoft.com/kb/822642 (scripting via sqldmo)
    Advanced: http://support.microsoft.com/kb/823938

Once you can connected with the SQL Management Studio (SQL 2005+) [or the SQL Enterprise Manager for SQL 7/2000], you should find the database folder easily. In SQL 2005+, the system databases are put into a dedicated folder, and you usually won't mess with those anyhow. On the context menu on the database, you can choose properties to see (and eventually change) the details of the database.
Note: If you want to move your database(s), you have two options:
To find all the logins defined in your instance, check the security/logins folder on your instance To create logins/users, refer to these two pages from Microsoft:
Starting with SQL 2005, there are new options available:
To know if SQL logins will actually work, you have to check if the instance is configured for mixed-mode authentication:Again, some quick notes:
To create a user in a database:  Open the properties of the login and go to the User Mapping tab. For each of the user databases that the login should be able to access, click the checkbox in the Map column, then you can override the username (which defaults to the login name), and add database roles if needed.
Usually, you don't need to map the login into the system databases.
 
If you want to know the details of the predefined fixed database roles, see here:
     http://msdn.microsoft.com/en-us/library/ms189612.aspx
If you want to know the details of the predefined fixed server (instance) roles, see here:
     http://msdn.microsoft.com/en-us/library/ms175892.aspx
You can create roles on database level only, and you should use that extensively, in most scenarios.

2. Connecting/Typical Tools

Let's look at some methods and tools you can use to connect to your SQL Server instances.
With SQL server 7 and 2000, you have the Enterprise Manager (EM):

...as well as the Query Analyser (QA):
As you can see, all you need to specify is the instance name and the login+password (or just specify Windows authentication) in the connection window.
 
Please note that it should not matter if you use the short Server name (NetBIOS), the full server name (FQDN), or its IP address. Only the network (firewall, name resolution etc) can make a difference there. If you connect to a named instance using the instance name, you do not specify the port.

Connecting When Using Command-Line Tools

For SQL 2000, there are two command-line tools:
Both use the same command-line arguments for connections:
Notes:
Starting with SQL Server 2005, both EM and QA have been integrated into a single tool, SQL Server the Management Studio (SSMS). There is no change in the how to connect compared to previous versions.

osql/isql in turn have been replaced by the sqlcmd command-line tool, the command line arguments for the connection are, surprisingly, exactly the same again.

Another command-line tool is bcp, which is used to import/export data from/into flat files. The connection options are again the same as for isql/osql and sqlcmd!

Connecting From Your Application

So that's all for connecting with the tools.  But mostly, you also need to connect from applications to your SQL server instances.

To start with, SQL Server itself can connect, from within its SQL language, to another instance. You can do that using either a linked server, or directly using OPENROWSET. Although those can connect to even other types of databases and not only other SQL Servers, what is important is that the linked server configuration defines the login credentials that are used when connecting to the remote database.

Again, you specify only the remote server/instance name, and in the credentials, you specify some login mappings, respectively a fallback login:
For applications, you have usually two methods:
    1) A method that accepts the same parameters like the SQL tools, or
    2) A way to specify a connection string.

Here are three links into a most valuable website, ConnectionStrings.com.  It's a great resource for helping you build a correct connection string (not only for SQL Server, btw):
    http://www.connectionstrings.com/sql-server
    http://www.connectionstrings.com/sql-server-2005
    http://www.connectionstrings.com/sql-server-2008

Some notes/terms:

3. Troubleshooting Connectivity Problems

Ok, you run into some connection problems... what to do/check?  Well, some of these might sound obvious, but the first things to check are:


Apart from that, I could just have post the following two links, and be done, as they cover all of  the usual issues:

    How to troubleshoot connectivity issues in SQL Server 2000
    http://support.microsoft.com/kb/827422 (for SQL 2000, but useful for understanding for 2005+):
...and...
    Potential causes of the "SQL Server does not exist or access denied" error message
    http://support.microsoft.com/kb/328306

Please make sure you carefully take each of the steps top-down.  In short:

Note: For a clustered instance, you use the cluster name or IP address instead of the server's name or IP address.

If you specify the server name, you can run into the name-to-ip resolution issues.

Usually, if that is the case, the connection times out after (default) 30 seconds as the name resolution (DNS lookup) fails.  If you get that, double-check if you specified the name correctly, and verify that the name is resolved correctly:
Once all issues on the underlying connectivity are solved, you can proceed to solving missing permission issues in the next step.

4. Troubleshooting Permissions

To connect to a SQL Server Instance, as noted, you use the login (and NOT the user) in the first place. When you connect to a SQL Server instance, you also connect to a database. This can be specified in the connection string, if it is not, the login's default database is used. This setting defaults to the master database.  To change that, use the

If the database requested is in any of the following states:
... then you will get a login error:
       Cannot open user default database.
       Login failed. Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064)

Solution: Specify the correct database name in the connection string, make sure the db is online, or add some permissions to the login (map the login to a user in that database)

Once the login has permissions to the database, and even if it's only the public role, the connection shall succeed.

5. You should be done by now!

I hope this article helped or helps you to solve your SQL server connection problems.  If you did not manage to solve your problem with this, just ask a question here at Experts-Exchange, in any of the SQL-Server-related Zones.    If it gets solved there, please post the link to that question here, so I could update the article accordingly.