July 26, 2007 at 11:45 am
Hi,
I need some help to setup Kerberos Authentication for SQL Server 2000. I believe by default Window authentication in SQL Server is Kerberos. But I don't know enough and have not come across any documentation that confirm this. I believe if both the server and client are on the same domain, when the client workstation connect to the server using windows security, this is consider kerberos. Is this true? Have any done this? Or is there a query that I can run to confirm what authentication I am using, like Kerberos?
My workstation and server are both Windows 2000 and I believe by default the kerberos support on windows are enable. My SQL Server is startup using local system account.
If you know of any documentation that show how to setup Kerberos authentication on SQL Server 2000 or know that my knowlege is true, please let me. I would greatly apprecaite it.
Thanks
Lousi.
July 27, 2007 at 5:26 pm
Hello,
SQL Server uses the OS security subsystem to provide network authentication. If you use a local windows account, NTLM will be used - not Kerberos.
If you use a domain account Kerberos will be used as the authentication mechanism automatically. Active Directory uses Kerberos authentication behind the scenes.
Herve
Herve Roggero
hroggero@pynlogic.com
MCDBA, MCSE, MCSD
SQL Server Database Proxy/Firewall and Auditing
July 28, 2007 at 11:46 am
It depends... but yes, in your curent configuration you should be connecting via Kerberos.
As for documentation, here is a Microsoft KB article on the subject:
How to use Kerberos authentication in SQL Server (319723)
However, your SQL Server should already be configured to use Kerberos. The Service Principal Names should be in place because of the fact that you're running under the local System account (in Windows 2000 this corresponds to the computer's domain account, so Kerberos does work).
As for verifying, from the client, the tool KerbTray.exe can show you the tickets your client has, meaning you can verify you are authenticating with Kerberos.
However, out of curiosity, is there a reason this makes a difference in your case since you're talking about a client connecting directly to the SQL Server?
K. Brian Kelley
@kbriankelley
July 30, 2007 at 2:20 pm
Brain,
So as long as the server and client are on the same domain and the services is start from local service account. Then I am using Kerberos authentication? I did installed the KerbTray.exe and I did see a ticket for Kerberos. I was hoping for a query that actually confirm that I am using Kerberos like what is offer in SQL Server 2005. select auth_scheme from sys.dm_exec_connections where session_id=@@spid
I have to setup this for our QA to test in our lab. We develop software that snip database traffic and report them for SOX purpose and also block certain harmfull SQL. Since we do have customer using Kerberos authentication, I have to make sure I have same setup so our QA team can perform their test. That is why my setup is simple using as much default as I can. It is very hard to understand all databases and their security setup. I am reponsible for Oracle, SQL Server, DB2, Informix, Sybase, MySQL & Teradata.
Thanks,
Louis.
July 31, 2007 at 8:03 am
Louis -
The main reason I use Kerberos is to make the hops between our web servers and the sql and analysis services. NTLM cannot make the hop.
Greg E
July 31, 2007 at 2:02 pm
I don't believe SQL Server 2000 has a way to tell this. However if your local security policy is set up right (whether configured on the server or through a group policy) for the following audit events, you should see when a user connects via Kerberos by looking at the Security event log for the server because the security event will tell you that's how the authentication was made.
One other point. Generally, using the local System account is frowned upon. If you want to use Kerberos, you'll need a domain user account and you'll need to go through the SPN setting exercise described in the article. However, if you stay with the local System account it should work because this is technically a domain account. System => Computer Account in the domain for a Windows 2000 server.
K. Brian Kelley
@kbriankelley
July 31, 2007 at 2:38 pm
Brain,
Thank you.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply