SQL Azure and Kerberos Authentication

Did you know that when you connect to SQL Azure, a KERBEROS authentication is performed? However, when using SQL Server Management Studio, or any other client, you are forced to use a SQL authentication mechanism.

While I was digging in the system tables, I realized that my SQL Azure connection was being reported as a KERBEROS authenticated connection.

If you run this statement on your SQL Azure database...

select

session_id, auth_scheme from sys.dm_exec_connections

... this is what you get: 

session_id   auth_scheme
160                 KERBEROS

However, in order to connect to SQL Azure you *had* to connect using SQL Authentication, for which the auth_scheme is SQL. So why do you connect using SQL authentication and end up with a KERBEROS authentication in SQL Azure?

Well, it happens that all connections to SQL Azure are proxied through a set of servers that perform the authentication handshake and the connection routing. So in reality, you are establishing an initial connection using SQL Authentication to a proxy, which then turns around and establishes a KERBEROS authentication for you to SQL Azure.  So while it looks like the client application is using SQL Auth, SQL Azure uses KERBEROS.

Pretty cool...

Print | posted @ Thursday, August 12, 2010 9:02 PM

Comments on this entry:

Comments are closed.

Comments have been closed on this topic.