SQL Server Reference Guide

Many programs don't have any connections that you have to configure. For instance, when you work with a document in Microsoft Word, the program handles all the connections to the hard drive subsystem and identifies everything it needs to open and work with the document.

In larger, more open platforms like an application against a database, it isn't quite as simple. The data isn't in a single, constant location, but instead most often is on a server across a network connection. In fact, there are multiple issues to consider, not just where the data is located. Database applications have layers of security as well, and not just at the database level.

In this overview I'll deal with some of the more common issues that you might encounter when you try to connect to SQL Server, and I'll cover some steps you can follow to find and fix them. I'll explain some of the issues you'll have in SQL Server 2000, and SQL Server 2005 and higher. I'll focus a little more on 2005 and higher, since that’s what most organizations are now using. The newer versions of SQL Server have more secure infrastructures, so you'll sometimes encounter more issues with those.

Basic Troubleshooting

To begin any troubleshooting exercise, you first need to isolate the problem to the proper component. That means identifying all the components in the environment and then ensuring that each one is functioning properly. When you find the component that isn't working, you simply correct it and see if you're able to connect. Sometimes you’ll have more than one problem, so you need to fix them one at a time to get to the next one.

That's often easier said than done, however. Sometimes there are so many variables involved with even one component that solving for each one would take a lot of time, which you don't have in an emergency. In other cases the variables change so quickly there isn't a stable state you can observe.

The place to start is to ask these two questions:

1. Was it working before?
2. What Changed?

The answer is normally “yes” to the first one and “nothing” to the second, which of course can’t be true. If it was working and now isn’t, then you need to find what changed. Perhaps it’s true that no one changed anything manually, but certainly something changed or broke to bring you to this state.

If it’s a change that someone initiated (changing a setting, stopping a service, rebooting a router) then make it easy for them to tell you. Sometimes people are afraid that they will get into trouble if they get “found out,” so they stay quiet. Meanwhile, you spend hours locating what they did. If you let them “off the hook” by not “pressing charges,” then maybe you’ll get the answer faster, which is what you’re after anyway.

On the other hand, perhaps no one really did make a manual change. That means that a state change occurred because of another system activity, or because something physically broke. The trick is locating that change. The fix for this problem is to eliminate as many variables as possible.

Of course, the first thing to check is all the logs. You should first open and examine the Windows Event logs. You should also check the SQL Server Error Logs, which are just text files. They are located in the SQL Server directory under Program Files, in a subdirectory called LOGS. Those two sets of logs will tell you at least the system state before things went wrong. After that, you need to do a little more investigating. Here are a few tips that will help you do that.

Find out how many applications are affected

Is this issue happening to everyone in all applications, or just a few? If the server is not responding to anyone, make sure you check the services on the SQL Server system to see if they are running. You can do that with the NET START command at a CMD prompt, or you can use the Services applet in the Windows Control Panel. Remember, any changes to the Services for SQL Server 2005 and higher should be done in SQL Server’s Configuration Manager tool, but checking, starting or stopping a service is OK to do from the Control Panel in Windows.

But don’t just start the services — check to see why they are not running. First, ask anyone you suspect that might have stopped them (other DBA’s, developers, system administrators, etc.) and then check the Windows Event logs. You can also check the SQL Server Error Logs. The reason you’re checking is in case there is a physical issue with the database or log files. If so, you want to stop what you’re doing and take a full system backup with the services down. That way, no matter what, you can’t make the situation any worse than it is. If you try and start the services blindly, SQL Server might actually try and recover the files on a damaged file system and destroy the files forever.

Once you’ve taken that backup, you can move on to more intense recovery processes, which I won’t cover here. If you’re not an Expert DBA at this point, it’s best to call Microsoft Support for help. Detail what the situation is, and explain what you’ve done up until this point.

If the server is responding to only one request, it might be that the Instance or database is set to single user mode. Open Enterprise Manager (SQL Server 2000 and lower) or Management Studio (SQL Server 2005 and higher) and right-click the Instance name and select Properties. You'll see there if the Instance is in single user mode. If you’re using SQL Server 2005 and higher, the administrator can still connect to the Instance even when it is in single user mode, using the Dedicated Admin Connection, or DAC. More on that is here.

Don't just change the mode back to multi-user — find out why it was set that way. There might have been a process that changed the state or perhaps another administrator changed it. This will be recorded in the SQL Server logs or in the Application Log of the Event Viewer in Windows. Once you've found the reason and corrected it, you can change the state of the database or Instance back to allow the users in.

If more than one group of users are able to access the database but others are not, find out what the differences between them are. Perhaps they are using a different network protocol or connection method. That may point you to the problem area.

If some groups of users are not able to get in but others are and the application, network and other settings are the same, you may be facing a permissions issue.

Try to connect directly on the server

One of the quickest ways to determine a networking or connection problem is to try and connect to the database directly on the server. Open a command prompt on the server, and type the following statement. This works on SQL Server 2000 and lower versions:

osql –e –d master

And this one works on SQL Server 2005 and higher:

sqlcmd –E –d master 

Note that if you’re using a named Instance, you’ll have to add the name of the server like this (my system is named Buck and my Instance is named SQL2K8)

sqlcmd –S Buck\SQL2K8 –E –d master 

Those commands will drop you in the SQLCMD command line, and you can type this:

SELECT @@VERSION
GO
QUIT

If this works, the server is responding. If it doesn't, then probably no one is getting in and you need to check the services for SQL Server to see if they are started. Once again, don't start them until you have checked all the logs to ensure you know why they stopped.

If that statement does work, replace the word "master" with the name of the application's database. Then type in a simple query that should work with your application. If those work, then you're back to an issue from the network connection out. From there try running the same query on a system that has SQL Server client tools installed, and you can rule out the network connection itself, and then investigate one connection from the application. If the second statement doesn't work, you need to investigate the database state. I’ll explain that in a moment.

Check Connections

I’ll actually mention this element throughout this article, since it can surface in a lot of different components, but once you’ve verified you can connect to the database on the server, then you need to find out why others can’t. The next logical step is to check the network settings.

The first thing to do is to see if the Windows server running the SQL Server Instance can connect to your network. You can use the ping command with the name or IP address of another system, or simply open a known network share on another system from the server. Make sure that you have the proper security taken into account — you don’t want to believe it’s a network issue when you just don’t have permission to the share.

Assuming that you can ping or connect out from the server, the next step is to see if you can ping in to the server from a client, or open a share on the Windows Server running SQL Server from another system.

If all of that works, then you might be facing a firewall issue. These are really not that hard to investigate, but the fix might be more difficult. Since you might be using one of any dozens of firewalls, I can’t advise you on the steps to troubleshoot that, but your vendor can. Books Online has a great article on firewalls and SQL Server settings that you can read here.

If you’re still not able to connect due to a network issue, it’s time to involve your networking professionals.

Use Graphical tools to investigate the server and database

Once you’ve used the command-line tools to investigate the system and ensure that at least the server is running, you can move on to a more in-depth investigation. When you’re in a hurry and things aren’t going well, it’s nice to use the graphical tools so that you can gather a lot of information quickly.

Start with the database state — you’re working from the “back end” forward to the application this way.

Database State Issues

A fully functioning SQL Server database has a "state" that it runs in, called ONLINE. In this state the database shows up as a normal icon in Enterprise Manager or Management Studio. There are other states, however, including OFFLINE, RESTORING, RECOVERING and so forth. If your database is in a state other than ONLINE, then you should check Books Online to find out more about why it is in one of these other conditions. Each condition has specific steps you should follow to correct the issue it represents, which I'll cover further in other articles here at InformIT.

Permissions Issues

I explain the basics SQL Server security more completely throughout this tutorial series, but I'll explain the basic login process here.

When a user tries to connect to a SQL Server database, they actually pass through two levels of security. The user must first exist as a server logon, and they also need access to a database through another user account. Most often the server and database account have the same name, but they are in fact different.

To make things even more complicated, SQL Server has two kinds of accounts. The first kind is created and maintained by SQL Server. In this case the user doesn't have to be recognized by the Windows system where the SQL Server platform is installed. If you're having connection issues, you need to make sure that the account is set up on both the server and the database as described in my series on security mentioned earlier.

In SQL Server 2000 and earlier, the control you have over the accounts created by SQL Server is pretty minor. You just have to ensure that the user is entering the name and password properly. In SQL Server 2005 and higher, you have a few more options, assuming the software is installed on a Windows 2003 server. In this configuration, you can have the accounts created on SQL Server follow some (but not all) of the same account policies that the server uses. In other words, you can lock out the account after a certain number of incorrect login attempts and so forth. If you have this configuration, you may need to check to make sure the user account isn't locked out.

Both versions of SQL Server can also leverage Windows accounts, whether they are created locally on the server or on an NTLM or Active Directory domain. In this case, you create the user on SQL Server by pointing to the Windows account name. In this configuration, you should make sure that the user isn't locked out of the Windows account before they try to access SQL Server. Remember, if an account is locked out during a Windows session, the user might not know it until they try to access a network resource, so don't take the fact that they are still able to use their system as an indication. Have them log out and back in to verify they aren't locked out of Windows.

Both of these cases have to do with the situation where some users can log in and others can't. If you're in a situation where no one can log in (including you), it still may have to do with permissions. Most people (for very good reasons) set the service accounts for SQL Server to a Windows account, rather than LocalSystem or NetworkSystem. If those accounts are part of the same policies that other user accounts are, then their passwords or durations might have locked them out as well. That means that they aren't able to log in, and so they aren't able to start SQL Server. You'll be able to see this when you try to start the SQL Server services and you continuously get a "log on denied" error in the Event log for those accounts.

Server Configuration Issues

All versions of SQL Server have various settings that allow clients to connect. When you try to connect to SQL Server, you will need to enable the same protocol the client uses. I’ve previously explained the tools to do that for SQL Server 2000, SQL Server 2005 and SQL Server 2008. In all of these, you have the ability to change the default TCP/IP port, and other settings for the various protocols installed on your server. You need to verify that the ports are set to what the clients expect.

In SQL Server 2005, the Surface Area Configuration (SAC) tool also controls even more basic settings, such as allowing remote connections. Check the previous link to ensure your settings are correct to allow clients in.

Client Issues

In addition to the server, database, network and configuration issues, you might have issues on the client-side. This can be anything from incorrect network settings to a firewall blocking the port the application uses. These kinds of issues are the easiest to locate, and the most difficult to solve.

They are easy to locate because you can verify the problem quickly by using the steps I mentioned earlier. Simply having the client logging on to another machine which works for another user will show you if the problem lies with the machine or the account.

They are hard to solve because a workstation's configuration is sometimes far more complex than a server. There are usually more drivers, user customizations and so forth. Also, any changes you make might damage the user's environment, so it is difficult to try things to fix the problem.

Your issue also may be with the configuration on the client. There are connection strings that you need to enter on most software to connect to your server. One of the most common connection errors is an incorrect connection string.

There's one other difficulty in working on a client's workstation. The first part of troubleshooting is to ask, "Was it working before?" If it was, the next thing to ask is "what changed?" We all know the answer. "Nothing", we hear, and we know that's not always true. Then it becomes a guessing game as you try to discover what has changed on the system.

This has been a short trip around your system, but the tips and information found here should help you discover the problem and point you to some possible corrections. The references below should help you find more information.

InformIT Articles and Sample Chapters

Jonathan Feldman has a good guide to Windows Network Troubleshooting that you can read for free.

Books and eBooks

If you’re just starting out with SQL Server, Eric Johnson has a good video tutorial, SQL Server Fundamentals for the Accidental DBA.

Online Resources

Microsoft has a great tutorial on connecting to SQL Server here.