July 26, 2007 at 4:44 pm
Anyone has experience setting up kerberos authentication on SQL Server 2005 on Windows 64 bit? It is working for me on windows 32-bit. I didn't need to do anything, but to make sure the sever and client are on the same domain. For the same setup on 64-bit server, it doesn't use kerberos authentication, but just windows authentication. If you have any luck setting this up on windows 64 bit, could you please share some note. Thank you very much
Louis
July 27, 2007 at 11:32 am
From my understanding, Kerberos is tried first, then if it fails, fallback is to NTLM.
You need to set up SPN's to register the services, which are slightly different on SQL 2005. And to do this you need to be a Domain Administrator. SETSPN.exe is the tool, or ADSI Edit.msc can be used.
MSOLAPSvc.3/Machine Name
MSOLAPSvc.3/Machine Fully Qualified Name
MSSQLSvc/Machine Name:port
MSSQLSvc/Machine Fully Qualified Name:port
These would be registered under the account the service is running under. We use a domain account to run these services.
Make sure the Machine is set up for delegation in Active Directory. Both the Machine and the User ID you use must be trusted to delegate.
run setspn -l Machine Name
expect to see HOST/Machine Name and HOST/Machine Fully Qualified Name
http://support.microsoft.com/?kbid=319723
Hope this helps. IIS is similar. HTTP/Machine Name and HTTP/Mahcine Fully Qualified Name.
I run my Application pool under a domain account vs. Network Service. So there are a few local policies to tweak, and User needs to be in the local IIS_WPG.
My test environment is a mix of 32 bit and x64 bit machines - setup was the same. Documentation is not the best - most you will find refers to W2000 and SQL 2000. Slight changes in names of services and maybe whether a port is needed or not.
Greg E
July 27, 2007 at 2:10 pm
Thanks Greg, I started my SQL Server 2005 using local service account. So, I don't have to do the SETSPN according to Book on line. Both the Machine and the User ID are trusted to delegate. I used the domain admin account to login to the SQL Server 2005 server. The documentation told me to download and installed Kerbtray.exe utility. I did this on my server and when I run it, I notice it just couldn't get a Kerberos ticket from my domain server. I did the same thing on my 32bit SQL Server 2005 and I was able to get a kerberos ticket using Kerbtray.exe utility. That is why I though may be this is a 64 bit windows issue. My domain sever is Window 2003 32bit. I don't know if this matter? May be I need to have a 64 bit domain server? I would hope not.
Do you remember if you have done anything different on 64 bit than 32 bit windows?
Thanks
Louis
July 27, 2007 at 2:24 pm
Make sure you register a SPN. Local System does not register one for SQL Server2005 as in previous versions.
Running as Local System is also a security risk. Please use a domain account. SQL Injection loves Local System.
And no - as I mentioned before, I run a mixed development environment when I set this up. Double check and see if you are running Local System or Network Service. Best Practice will be a domain user account for security and trackability.
Greg E
July 30, 2007 at 9:21 am
Greg,
I tried creating SPN for SQL Server 2005 and startup the database using the domain adminstartor account for the service. I am still connecting using NTLM not Kerberos? I think my problem is that my SQL Server server couldn't even get a Kerberos ticket from the domain server. The only think I question left is does the domain server need to be a 64-bit machine? My domain server is 32-bit and my 32-bit SQL Server work just fine with Kerberos. Can you check to see if your domain server is 64-bit?
Thanks,
Louis
July 30, 2007 at 9:47 am
Louis -
Usually a Domain Administrator Account would not be trusted for delegation. This would be a very bad account to use. The SQL / SSAS machine also has to be trusted for Delegation.
Our DC is 32 bit. The only x64 bit machines on our network are the 2 we use for SQL / SSAS.
Run SETSPN -l ServerName for the machine. Do you see mssqlsvc entries for both NetBios and FQDN? MSOlapsvc3 entries? And for the ID?
Greg E
July 30, 2007 at 2:24 pm
Thanks Greg,
I believe I found out our problem it was some DNS naming issue. My setup and your setup work. The default on everything does work without using domain using to startup a service. I am doing this for testing our product in the lab only. So, that is why I didn't use a normal domain account to startup a database service and so. Thank you for all your speedy reply
Louis.
September 30, 2007 at 6:19 am
Hey there could you tell me why you use the kerbos.
We are trying to use AD with SQL Server and all web pages uses service accounts. I will be adding this to SQL Server as windows domain\myservice account.
The problems comes when pages are displayed and in sysprocesses i see domain\my service account
However when jack use the web page (i want to prevent him seeing data in one of the tables) but as ther domain\myservice account has data reader he sees all the information.
How do i tie jack who logs in to the service account
In ad jack is part of a group and this group is assigned to the service account but in sql i have no way of knowing he came in just the service account.
Thanks
September 30, 2007 at 10:18 am
Kerberos is used to authenticate using the windows credentials. It is used to hop between servers. We have the web application on a separate server from SSAS, and SQL on another server. The user sees only what they are authorized to. It generates a ticket that is used based on who is logged on to the local machine. Connection strings use SSPI for data access, not something hard coded in.
No one should log in using the service account. I'd consider changing the password to this account.:cool:
Read up on security. Microsoft has some rather detailed documentation concerning this, and if you are using .Net framemwork, pay close attention to what accounts are being used.
You also might consider removing Built In Adminstrators group. If someone is Admin on the sql machine, they have everything.
I'm in the BI group, and we have to limit what people see on a regular basis. No expert on security, but have learned a lot in the last few years.
Kerberos is mainly used for intranet. If you are using WWW, you can leverage possibly still use it from what I understand. Search for Kerberos Protocol Transistion.
Hope this helps you out.
Greg E
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply