how to choose security mode in an enterprise scope

[masds]

I know the best is windows authentication mode, so I thought we might need choose windows auth for any newly installed SQL Server, and still stick to mixed mode for all the existing SQL Servers because the change may involves applicaiton code change, also there are some SQL Server 7.0 is in use and using mixed mode, Is this a feasible approach?

by the way, what's the difference between 2000 and 7.0 in authentication mode?

 

[mrdenny]

There isn't any difference between 2000 and 7 when it comes to the authentication modes. There is some difference between the hash methods when encrypting the password.

Your approach is feasible, however it will only work if the applications being hosted on the server support windows auth only. If they don't then you'll need to change it to mixed mode.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[masds]

however it will only work if the applications being hosted on the server support windows auth only. If they don't then you'll need to change it to mixed mode.

You mean the application that access the SQL server not using ODBC? If using ODBC, then the trusted connection can be used. Another example is ASP application, if the IIS service access another COM which in turn access the SQL Server database, the COM can be se tup to running as a windows local user or a domain user, so the windows auth can be used here. I'm not sure here how the application split all the end users into different group if all of them map to one windows user?

 

[Catadmin]

What Denny is saying is if any of your applications use a connection string that maps to a SQL Login that is set up as an Application Role (which is not usually mapped to a windows user or group) or if it uses a regular SQL Server login that, again, hasn't been mapped to a Windows User or Domain Group, then you will be unable to use those applications in Windows Only Authentication Mode.

Of course, the only way you know this is when you turn it to Windows Only mode and something breaks or if you go through all of your apps and verify HOW they connect.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[masds]

So we need to know more about the applications in the real world, I know ASP.NET application can use windows auth, maybe it's awkward to use windows auth in classical ASP, I guess most of the web application won't support windows auth very well. So what's the point to say the best security solution is to use windows auth? Like denny said, in a perfect world the windows auth is the best, but the world will never be perfect, then we will never use windows auth.