SQL Server Performance Forum – Threads Archive

ODBC Authentication

For years we have used SQL Authentication when creating DSN’s. Yet I always read that it is more secure to use Windows Authentication and it is obvious why. My issue is that we use these DSN’s on our servers so the Web App can communicate with the database (CF based). If the DSN was created to use Windows Authentication how can it communicate when no one is logged onto the server where the DSN resides…. Follow me? This is the actual text from the ODBC Wizard which is no help in understanding this. I am stirring up something that works well but we are about to move our databases to a new server and therefore must recreate the DSN’s so I figured why not do them the best way possible. <G> Thanks Doug ODBC help text Specifies that the SQL Server ODBC driver request a secure (or trusted) connection to a SQL Server running on Microsoft Windows NTÂ® or WindowsÂ® 2000. When selected, SQL Server uses integrated login security to establish connections using this data source, regardless of the current login security mode at the server. Any login ID or password supplied is ignored. The SQL Server system administrator must have associated your Microsoft WindowsÂ® login with a SQL Server login ID.

Depending on the technology you use, you can have your data connection components run under an NT account. If they are running under a specific NT account, you need to grant that login access to SQL Server. The DSN will use that "trusted account" when the application accesses it to access the SQL Server. Nobody has to be logged into the server. The only time this was not possible was with old RDO components. You had to have the machine logged in as a user at all times for it to work. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

The SQL Server ODBC driver allows you to configure datasources to use either Microsoft Windows NT authentication (integrated security) or SQL Server authentication to log into the database. The 2000.080.0194 version of the driver ignores the authentication specified in the data source name (DSN), and attempts to log into the database with NT authentication. KBAhttp://support.microsoft.com/default.aspx?scid=kb;EN-US;247931 information about authentication methods from ASP. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Maybe I wasn’t clear. I am aware of this info and thank you. How about this approach. Is it true that Windows Authenticationm is a more secure method to communicate with a SQL Server from a Web Application? I am NOT using ASP. WE use ColdFusion which in CF 5.0 has it’s own front end to the ODBC admin. I still see no way to specify the Windows user within the ODBC administrator. That is my question. Is there a way to specifically specific a user? I cannot have them change the app (they won’t anyway). TIA Doug

Yes Windows authentication is more secure and preferrable than Mixed mode from any web application. And if you use windows authentication there is no scope of specifying specific user in ODBC. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

As mentioned you do not specify a user when using Windows Authentication. The account that the application is running under (the coldfusion or IIS service in this case) will be the windows account that need access in sql server. For example IUSR_… or any other account that you specify to run the service. If you have multiple sites it’s recommended that each site run under it’s own windows account and don’t share the IUSR_… account.

]]>