February 20, 2007 at 11:51 am
I'm looking for a white paper regarding whether to use Windows authentication versus SQL Server authentication for an application. Especially anything that defines the threading process. Anyone have some info on this?
Thanks,
Lezza
February 22, 2007 at 9:14 am
Windows authentication is almost always preferred to SQL authentication. It is more secure, and means less work for the DBA maintaining logins. If a limited group of users are allowed access to the application, then a Windows domain group can be created and only this group given rights to the database used by the application.
You usually only resort to SQL authentication when the users are in multiple Windows domains, or there are other security requirements.
If the application users have various levels of permissions on the server but you want to prevent them from any kind of ad-hoc access to the application database, you can use an application role. The users need to have basic access to the database, but no rights to any object. Then code in the application can invoke the application role, and the application can do everything it is designed to do. Note: application roles apply to a connection, so special care must be used with any kind of connection pooling. Typically you add something unique to the ApplicationName attribute in the connection string so it will not be shared.
March 1, 2007 at 12:57 pm
If I may, I have an additional question about this.
Are there are application response issues with using one authentication method over the other?
I actually was asked this question yesterday.
March 1, 2007 at 3:09 pm
There is no effect on application response.
The first major difference is that the DBA gets involved in maintaining users & passwords with SQL authentication, and applications need to have SQL names & passwords built in rather than just using a trusted connection. If you want to use bcp or sqlcmd in a batch file, you can use a one-letter argument to specify trusted connections or you can spell out the SQL user and password.
The next big difference is if you want to do anything over your SQL connection that requires the server to access network resources, such as linked servers or access to file servers for bulk inserts. It can use your Windows credentials to do this transparently (in a properly-functioning Active Directory domain with Kerberos), but if you use a SQL login you will probably need to create credentials and maintain extra linked server security details.
March 6, 2007 at 5:28 pm
It's interesting that you say there is no effect on using either a Windows Account or a SQL Server login, as what I'm looking for is a white paper that tells about the threading problem with using a Windows account that doesn't occur when using a SQL Login. This may not be a white paper, but some other type of documentation. This is the reason why most applications use SQL SErver Logins instead of Windows logins. I'm looking for the definitive information. Thanks...
March 7, 2007 at 7:20 am
While the list of things I don't know keeps growing daily despite my best efforts, I'm having trouble with the notion that the type of login used for a SQL connection somehow affects server response time or application threading behavior. It can affect how connections for multiple threads are pooled however. A single thread using a single connection will not see any performance difference related to login type. With connection pooling turned off, a thousand threads using a thousand separate connections will not see any performance difference related to login type. With connection pooling turned on, Windows logins for the same server will probably have identical connection strings and may be pooled together. Multiple connections with the same SQL login may also be pooled together, but connections for different users will be in separate pools. If this is the issue you're trying to deal with, you can look into configuring the size of connection pools, or you can differentiate Windows login connection strings by adding distinct ApplicationName attributes to control which connections are in the same pool.
Another login-related problem with mutithreaded apps and pooled connections is if the user for a connection is changed with application roles or SETUSER/EXECUTE AS, it affects other connections in the same pool. This has nothing to do with SQL vs Windows logins, and I doubt that it is what you're looking for.
I'm not sure where the idea that "most applications use SQL Server Logins" comes from as it goes against everything Microsoft has to say about security, unless you qualify the statement as "most web applications". Web servers are often located in a DMZ outside the Windows domain where the SQL Servers live, so they often have little choice but to use SQL logins.
September 30, 2007 at 6:46 am
Trying to use NT rather than SQL.
Web App uses nt domain\EMPLOYEE_service_account.
AD Group called webgroup
In sql i add the domain\EMPLOYEE_service_account.
I grant data reader on the table.
This means who ever is in webgroup sees all the tables and all the data. So our web developers (see data in employee tables that are sensitive information like salary and ssn).
Using database role i can limit the columns here in table employee
The thing im stuck on is
(Im trying to figure out if web guy jack runs select * from employee) he only sees employee name (and not ssn salary).
I may want john who is senior web guy (he can see ssn salary).
I cannot determine the way to do this.
Any light be appreciated.
Is there a way in SQL to say this is DOMAIN\EMPLOYEE_service_account
then under here you have role (read employee data) (jack)
role (not read data) junior guy
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply