Using integrated connection between a web application and SQL Server

Yesterday afternoon, there was some discussion about where to store the database connection password in a web application environment. A tough question... Some actually proposed different stores mechanisms, providing password persistence: text files, encrypted strings, registry, active directory and so on. It was quite funny as each one had his own 'best practice'.Everything went well, until some substantial geek (you know, the friend you wanna have for urgent needs but not show to all your other friends) asked "why don't you use integrated authentication ?"

I guess Earth stopped turning at that moment. What I considered being a standard, especially when using Microsoft based web development environments, was actually unknown to most of the developers sitting around the table.

My friend explained to them it was some implicit kind of authentication, which didn't require the developer to store the clear text password anywhere. Well, I thought it might of use for some people if they don't know this yet.

Using integrated authentication with SQL Server

• The process is quite straightforward:
• first, the database connection string is modified according to integrated security;
• second, the database is configured to allow access to the web service user;

First, a minimal connection string

(SQL Server connection strings)

Standard connection string:

Server=192.168.2.20;User=usr_database;Password=miami6000;Database=web;

New version with integrated authentication: no password is transmitted and the default catalog is chosen:

Server=TOKYO;Integrated Security= SSPI;

What has happened here ?

As you can see, the new connection string leaks far less information. We don't know the server ip address, there are no credentials, and the database instance or catalog is chosen by default. This might not be the holy grail of security but could at least require more time to a hacker in order to get this information.

Next, giving database access to the web service user

Integrated security basically means: "I am authenticated therefore I am who I pretend to be. You don't need my credentials anymore." According to this, you only need to grant access to the database to the user on which the web application is running.

In ASPNet applications, the holy user is ASPNET.

Open an osql shell (type 'osql –E' in a command shell) and enter the following commands: (don't forget to replace 'MACHINENAME' by your machine name, and 'mydatabase' by your database identifier)-- connect to the database

USE mydatabase
GO
-- declare the user
CREATE USER ASPNET FOR LOGIN [MACHINE\ASPNET];
GO
-- grant connection rights
EXEC SP_GRANTLOGIN 'MACHINE\ASPNET'
-- grant data access and modification privileges
EXEC SP_ADDROLEMEMBER 'db_datareader', 'ASPNET'
EXEC SP_ADDROLEMEMBER 'db_datawriter', 'ASPNET'
GO

From now on, the web application service user has data read and write privileges (but no administrative rights) on the unique database he needs working with.

Conclusion

This article showed an example of using integrated connection instead of username/password based authentication. With the advantage of preventing transport of credentials across the network, it also prevents a hacker from getting access to full credential information (username and password). Moreover, the web service user has, in this case, only the strict minimal rights with the database: reading and writing data.Almost any web application technology provides integrated connection in a way or another. I don't know how it works with PHP/MySQL or JAVA/Oracle for example but I guess it should be the same...See you next!