Database Reference
In-Depth Information
More parameters are available than the ones shown here, but you get the idea. A corollary of
Murphy's law applies to connections: If several things can go wrong, surely one of them will. Your goal is
to check both sides of the connection to make sure all of your assumptions are correct and that
everything the client program specifies is matched correctly on the server.
Often the solution is on the server side. If the SQL Server instance isn't running, the client will be
trying to connect to a server that doesn't exist. If Windows Authentication isn't used and the user name
and password on the client don't match the name and password of a user authorized to access the SQL
Server instance, the connection will be rejected. If the database requested in the connection doesn't
exist, an error will occur. If the client's network information doesn't match the server's, the server may
not receive the client's connection request, or the server response may not reach the client.
For connection problems, using the debugger to locate the line of code where the error occurs
usually doesn't help—the problem almost always occurs on the call to the
Open
method. The question is,
why? You need to look at the error message.
A typical error is as follows:
Unhandled Exception: System.ArgumentException: Keyword not supported...
The cause for this is either using an invalid parameter or value or misspelling a parameter or value
in your connection string. Make sure you've entered what you really mean to enter.
Figure 12-2 earlier showed probably the most common message when trying to connect to SQL
Server. In this case, most likely SQL Server simply isn't running. Restart the SQL Server Express service
with
net start mssql$<your sql instance name>
.
Other possible causes of this message are as follows:
•
The SQL Server instance name is incorrect. For example, you used
.\sqlexpress
,
but SQL Server was installed with a different name. It's also possible that SQL
Server was installed as the default instance (with no instance name) or is on
another machine (see the next section); correct the instance name if this is the
case.
•
SQL Server hasn't been installed—go back to Chapter 1 and follow the instructions
there for installing SQL Server 2012 Express.
•
A security problem exists—your Windows login and password aren't valid on the
server. This is unlikely to be the problem when connecting to a local SQL Server
Express instance, but it might happen in trying to connect to a SQL Server
instance on another server.
•
A hardware problem exists—again unlikely if you're trying to connect to a server
on the same machine.
Security and Passwords in SqlConnection
There are two kinds of user authentication in SQL Server 2012. The preferred way is to use Windows
Authentication (integrated security), as you do when following the examples in this topic. SQL Server
uses your Windows login to access the instance. Your Windows login must exist on the machine where
SQL Server is running, and your login must be authorized to access the SQL Server instance or be a
member of a user group that has access.
