Why Can't I Log Into SQL Server?

  • [アーティクル]

Even though I've been working on the SQL Server protocol layer for the past 3 years I still have those days where I scratch my head because I cannot connect to my SQL Server.   I have to agree it can be frustrating sometimes.  Usually, once you get it working, it stays working, which is a good thing.   If any of you out there hit any other "head smacking" gotchas, let me know.

1. SSL Self-Signed Cert Does Not Work When Forcing Encryption From Client

Today I forgot that I checked the "Encrypt Connection" checkbox in SQL Server Management Studio while trying to verify that an SSL certificate I installed on a SQL 2000 box a day before was working.  Then I attempted to connect to my SQL 2008 server that is using self-signed certificate (which is default behavior in SQL 2005 and later).  When you do this, you get the following error:

"A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (Microsoft SQL Server, Error: -2146893019)

This error makes sense if you realize that a self-signed certificate does not have a trusted root (everyone knows this, right?).  Anyway, if you see this one, now you will remember.  This can occur with SQL 2005 and SQL 2008, both of these will load a self-signed certificate during startup time if an appropriate SSL certificate is not setup on the machine.

Thing to remember:  If you want full client side forced SSL encryption to work properly with SQL Server, you need to configure a real SSL certificate that the client trusts.   The self-signed cert is only useful for encrypting login packets and is not fully trusted by the client since it does not have a trusted root authority.

2. I'm A Vista Admin But Cannot Login To My Local SQL Server

Another good one:  Attempt to login to your local SQL Server on Vista from non-elevated command prompt or non-elevated SSMS.  Login failed for user domain\username!  But I'm logged in as an admin!  You are not admin if you don't elevate.  This one get's me about once every 3 months or so.

3. Cannot Connect To A Clustered SQL Server Named Instance From A Vista Client (submitted by Andy Babiec, thanks!)

This is technically speaking not a Vista specific issue, but it is seen more often on Vista clients because of it's advanced (a.k.a. more secure) firewall configuration.

The basic problem is when the client needs to determine the port of any SQL Server named instance, it will send a UDP packet on port 1434 to the target server and then wait for a response on UDP 1434.  The response packet will have instance name and port number.  Now the issue arises with Windows Cluster where our SQL Browser service gets the incoming  UDP packet then posts a response.  The incoming UDP packet comes in on the virtual cluster IP, but when we send a response, the packet is tagged with the local machine's IP.  Hence a more aggressive client firewall may block the response UDP packet since it has a different IP from the outbound request packet.

This can cause clients on Vista such as SQL Server Management Studio and also Sql Profiler to fail to connect to the clustered named instance.

There are various workarounds, one is to add SSMS.exe and SqlProfiler.exe to firewall exclusion list.  Another is to specify the tcp port in the connection string when connecting.  I tend towards the latter, but then again I am a protocols geek and I setup my servers with known port numbers.  We're working with the Windows Cluster team for a solution to this one.  Here is a more detailed blog on the subject from Xinwei, one of our protocols guru:

https://blogs.msdn.com/sql_protocols/archive/2006/02/27/unable-to-connect-to-a-sql-server-named-instance-on-a-cluster.aspx

 

4. User Does Not Have Right To Log Into Their Default Database

I have seen this a few times on the newsgroups, so adding this one too.  When you create new users in SQL Server, be sure to allow them access to their designated default database, otherwise they cannot log in! 

For example, create a junk1 user like so:

use master
go
create database junk1
go
use junk1
go
create login junk1 with password=N'junk1',
default_database=junk1, check_expiration=off, check_policy=off
go

Now attempt to log in using osql:

C:\>osql -Ujunk1 -Pjunk1 -S.
Login failed for user 'junk1'.
Cannot open user default database. Login failed.

If you examine the errorlog in SQL 2008, you see:

Error: 18456, Severity: 14, State: 40.
Login failed for user 'junk1'. Reason: Failed to open the database specified in the login properties. [CLIENT: 123.23.123.12]  

We actually did a ton of work in SQL 2008 to make these 18456 login errorlog messages more human understandable in SQL 2008, so this is nice feature for dba who is wondering "who is trying to log into my server?".  In SQL 2005 you need the special 18456 decoder ring:

secretcodering

Comments