SQL Server Performance Forum – Threads Archive

SQL Server Authentication Mode

My company has a public web site that is not in our domain. The back end of the web site is SQL Server 2000 which is in our domain. Right now, we use mixed mode. Is it better if we change it to use Windows Authentication Mode? Thanks very much for your help.

Yes you will have more security to use Windows authentication. Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

As Luis Martin said, its better to change the authentication. But before that, please check is there any clients/users are accessing the database using non-MS-windows, through client applications. If so you won’t able to use windows authentication. -Johnson

Yes you need to have windows 2000 on all clients as it can not get authenticated with user running application on Windows 98.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsentpro/html/veconsqlserversecuritymodes.asp explains about security models to choose.
Windows Authentication takes advantage of Windows NT user security and account mechanisms. This security mode allows SQL Server to share the username and password used for Windows NT and allows the user to bypass the SQL Server login process. Users with a valid Windows NT account can log in to SQL Server without supplying a username and password. With Windows Authentication we recommending placing users into NT groups and adding the NT group login to SQL Server. This powerful feature allows you to group your users together at the NT Domain level and apply permissions at the SQL Server level. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks all for your input. I have another concern: Because we have several databases, if i put user accounts for each database to NT group and add the NT group login to SQL Server. How could i give different user permission to each database. It looks to me if i add the NT group as SQL Server login, and i can only give database permission to group, not group member.

You can add individual NT users to database and/or server roles, exactly the same as NT groups! As long as those users do not belong to NT groups that are member of other database and/or server roles, everything should be transparent. You could also create NT groups specifically for mapping groups of users to database and/or server roles.

True, refer to the ROLES topic under books online for more information and code examples.
http://www.databasejournal.com/features/mssql/article.php/1441261 fyi. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>