Authentication an application using Windows Integrated Authentication

Hi all,

My work is using a shared application which accesses a MSSQL 2000 database. To access the application, the folder on the Windows 2003 Server is shared and users can access the folder through a shared drive.

For the application to access the database, it uses an ODBC connection to the MSSQL server which originally used the SA password.

We have recently switched to using Windows Integrated Authentication because we believe it offers a higher level of security. However the only way in which we have been able to enable this is to add the windows users to the SQL server.

The problem with this is that the application sets permissions for individual users on what records they can see within the database. We have found that by adding the windows users to the SQL Server, they can bypass the permissions the set by the application by simply using any application that can use an ODBC connection, such as Enterprise Manager, and see all the database.

One way around this would be to set up domains of users with access privileges to the tables which reflect the permissions set by the application, and configuring a view of the data so they may only see the records that they have permissions to. However to do this would require a high administrative cost to ensure that changes made in the application are reflected in the privileges of the SQL server.

Instead, is there a way the SQL server can authenticate that the ODBC connection is coming from the correct application using Windows Integrated Authentication?

This would allow the applcation to determine security, and stop users from connecting to the SQL server using other applications.

Alternatively, can the SQL server, using Windows Integrated Authentication, also ask the application to supply a username and password?

Any help with this matter would be greatly appreciated.

Thanks!

The answer to both your questions is no. Windows authentication does not authenticate the application that made the connection request, it just authenticates the context under which the connection request was made. Also, the whole purpose of Windows Authentication is to remove the need to provide a password, so if you want to use a password, you should just continue using SQL Authentication.

Also, your application should not control database access within itself. Database access should be controled in the database or in a mid-tier, not within the client application.

Thanks
Laurentiu

|||

Thanks Laurentiu,

I would love to have control over the application itself and do it and a more securely, but we didn't create the application, and trying to get the vendor to do it is like pulling teeth.

What I'm really after is the best security configuration for the application which will provide the highest level of security, and more importantly please my manager :)

The only things we have control over are whether the application uses the SQL Password or Windows Authentication, and who can access the folder with the application in it.

When using the SQL Password, the application can only log into the database using the one account, which basically must have rights to do everything.

Using Window Authentication, I have to add individual users to the SQL Server to allow the application to access the database when they are using the application. However, this will allow the user to use other applications to access the server, since they have rights to it.

What do you think is the best configuration?

Thanks.

|||

Where does the application store the sa password? If your users can easily get to it, they can connect as sa. Also, if they can debug the application, they can get the password and connect directly as sa. So the drawback of having the application connect as sa is that your users could figure out the sa password and can then become admins.

If you use Windows authentication, the users could connect on their own to the server, but they would probably not be administrators (unless the application makes any Windows login that is used with it an administrator).

I'm not sure which one of these you would prefer.

Thanks
Laurentiu