 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Security / February 2005Windows Authentication in a NT domain vs in an Active Directory
I'm changing an application which is connecting to SQL Server through SQL Authentication so that the application can connect to SQL Server through SQL Authentication or Windows Authentication. It seems that I just need to provide a UI for the application administrator to enter which Windows Users should be added to the SQL Server logins. Then the application will add those logins as users to databases and grant the necessary permissions to those users. Will there be a difference if the SQL Server is in a NT domain vs in an Active Directory environment? If this is not a normal way to make an application to connect to SQL Server using Windows Authentication, how should I do it? I'm not changing the application so that users can login Windows and then run the application without logging to the application (single sign-on). The application will still require users to log on to the application by user id and password which is setup in the application. Thanks. NT Domain vs Windows AD Only real SQL change is that you can publish certain object to Active Directory if you chose. Authentication to the server from SQL perspective is unchanged. New options with AD: 1. Kerberos authentication 2. Security Delegation Possible now Thanks, Kevin McDonnell Microsoft Corporation This posting is provided AS IS with no warranties, and confers no rights. Hi Kevin, My understanding is that Kerberos Authentication is the default authentication on computers with Windows 2000 or Windows 2003. So, we don't need to setup anything in SQL Server or Active Directory for Kerberos Authentication. Security Delegation is actually part of Kerberos Authentication. However, it can be controlled in Active Directory. When a SQL Server is installed on Windows 2000 or Windows 2003 (regardless the existence of Active Directory), it has an additional tab Active Directory when you open SQL Server Properties dialog box. > NT Domain vs Windows AD > Only real SQL change is that you can publish certain object to Active [quoted text clipped - 11 lines] > > This posting is provided AS IS with no warranties, and confers no rights. Yes. This is true. Clients capable of Kerberos will attempt to connect via Kerberos to SQL Server if you're using Windows Authentication. If the Kerberos attempt fails, the client will use NTLM. We don't log anything in SQL to tell you that the connection was made via Kerberos or NTLM. Yes. Security Delegation is an option to allow credentials to be passed from one machine to another. This was not possible in an NT 4 domain. The typical scenario where this is used is a Web Server application that connects to SQL via Trusted Authentication. The web client is able to authenticate to IIS via Kerberos, and then make a Kerberos connection to SQL using the client credentials. The SQL Server has to have the SPN set by a Domain Admin in order for this to work correctly. Thanks, Kevin McDonnell Microsoft Corporation This posting is provided AS IS with no warranties, and confers no rights. Hi Kevin, 1. Does SPN exist for a Windows 2000 server or Windows XP machine in a Windows NT domain? How about in a Windows 2000 domain without Active Directory? 2. How to use the setspn.exe to create and list SPN for an instance of SQL Server (e.g the server instance is PETER\TEST1, domain name is W2KDOMAIN, SQL Server service is using W2KDOMAIN\PETER to start the service)? 3. Same as #2 except SQL Server service is using local system account to start the service. Thanks. > Yes. This is true. Clients capable of Kerberos will attempt to connect via > Kerberos to SQL Server if you're using Windows Authentication. [quoted text clipped - 16 lines] > > This posting is provided AS IS with no warranties, and confers no rights. Responses inline: 1. Does SPN exist for a Windows 2000 server or Windows XP machine in a Windows NT domain? How about in a Windows 2000 domain without Active Directory? --- Not in a Windows NT domain. SPN's will exist for the hostname for machine in AD. SPN's don't exist for SQL unless the service is running under localsystem. Which is not recommended. Only the Domain Admin has privleges to add a new SPN for SQL. Also, you can't add SPN's for a server with Dynamic ports because the port number is part of the SPN. The server must be using Static ports. 2. How to use the setspn.exe to create and list SPN for an instance of SQL Server (e.g the server instance is PETER\TEST1, domain name is W2KDOMAIN, SQL Server service is using W2KDOMAIN\PETER to start the service)? --- Setspn -A MSSQLSvc/VirtualSQLServerNameHere.W2KDOMAIN:PortNumber Peter See the kb for example. 319723 INF: SQL Server 2000 Kerberos support including SQL Server virtual http://support.microsoft.com/?id=319723 3. Same as #2 except SQL Server service is using local system account to start the service. localsystem is not recommended for Standalone service accounts, nor Clustered Servers. It should be a domain account per the following article on Virtual SQL Server accounts. 239885 How to change service accounts on a SQL virtual server http://support.microsoft.com/?id=239885 Thanks, Kevin McDonnell Microsoft Corporation This posting is provided AS IS with no warranties, and confers no rights. Hi Kevin, Thanks for your reply. Peter > Responses inline: > [quoted text clipped - 40 lines] > > This posting is provided AS IS with no warranties, and confers no rights. You're welcome Kevin McDonnell Microsoft Corporation This posting is provided AS IS with no warranties, and confers no rights. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.