Kerberos setup on windows 64-bit

  • Louis Lam-391748

    Ten Centuries

    Points: 1078

    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

  • Greg Edwards-268690

    SSC-Insane

    Points: 20586

    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

     

  • Louis Lam-391748

    Ten Centuries

    Points: 1078

    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

  • Greg Edwards-268690

    SSC-Insane

    Points: 20586

    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

  • Louis Lam-391748

    Ten Centuries

    Points: 1078

    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

  • Greg Edwards-268690

    SSC-Insane

    Points: 20586

    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

  • Louis Lam-391748

    Ten Centuries

    Points: 1078

    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.

  • TRACEY-320982

    SSChampion

    Points: 13652

    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

  • Greg Edwards-268690

    SSC-Insane

    Points: 20586

    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 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply