Authentication question

Greetings from an SQL Server newbie.

We are purchasing 3rd party software with a SQL Server back end. A tech person installing SQL Server on the server insisted we use mixed mode authentication. (We are running Windows 2003 using Active Directory) We want Windows authentication (WA) but he claims it is "more complicated" to use.

His example: A shop using WA found that users running a certain process that creates and deletes temp tables did not have permission to delete tables and the process failed. The vendor had to go in and manually delete the tables.

The application uses .INI files with passwords and user names in plain text to connect to SQL Server. Our security specialist's eyes bugged out. :eek: We all think this is not good.

Is the vendor blowing us smoke? Should we insist on WA?

Thanks for your help.Depending on the package, what you want your users to do, network topography, and other issues they might have a point.

Just a few weeks ago some of the folks at a major manufacturer in Moline were having a fit about a package that had been developed internally (by a plant manager) that used SQL authentication. The central admin folks were having a fit, until they discovered that the reason the app was written that way was due to limitations in their internal network that made AD impractical. Sometimes even when you have the source code, other conditions prevent you from being able to make the changes that you'd like!

Even Microsoft came to the conclusion that SQL Authentication was the best option for those conditions, as much as it irked them to have to say that!

-PatP|||The vendor's example smacks of bogosity. Windows authenticated users, and SQL authenticated users can both be granted and denied the same set of permissions. Once a login is authenticated, there is no difference between them. The major reasons we would go with SQL authentication is for webservers. Generally, webservers run several applications (web sites), and each may need their own set of permissions on a set of databases (usually one DB per application). Windows authentication allows for only one login for the server, while SQL authentication allows each application to have its unique login.|||Exactly the problem that they were having. Pseudo-public machines force all users to share a common NT login. If you want users to have different levels of permission, you have to use SQL authentication.

The same problem applies on a more general scale to a WAN that doesn't support NetBIOS completely. Since the workstations can't authenticate over the WAN, the credentials have to be forged... Because they can't authenticate, they all get the same credentials. Same problem as before, but LOTS harder to fix!

-PatP|||So I guess sometimes it makes sense to use SQL Authentication instead of Windows Authentication. But it makes no sense to store username/passwords in an .ini file as plaintext. Perhaps it says someting about the effort put in by the vendor.|||So I guess sometimes it makes sense to use SQL Authentication instead of Windows Authentication. But it makes no sense to store username/passwords in an .ini file as plaintext. Perhaps it says someting about the effort put in by the vendor.I see that as pure idiocy, not really a database issue at all. I'm flat out allergic to storing passwords in plain text anywhere, and generally uncomfortable with storing them on PCs at all (with the exception of tools like SplashId or Password Safe that are designed for just that purpose).

-PatP|||hmmz.. pwsafe comes in handy and secure indeed. btw: it comes with an export-function to plaintext ;)|||hmmz.. pwsafe comes in handy and secure indeed. btw: it comes with an export-function to plaintext ;)I suppose that all tools have to have some flaw! ;)

-PatP