SQL Server Security Tips and Tricks
Several readers wrote in with their ideas and approaches to security, working with what amounts to mixed security environments and the like. I thought I’d pass along some of them here – feel free to send in your thoughts and approaches as well.
Roger: "We run multiple web applications against SQL Server sometimes with own application database, sometimes more than one application sharing same data store.
For an IIS hosted web site we set the web site and app pool to run as an application specific Windows user, this allows us to use Windows authentication for all IIS to SQL Server communication as well as file system ACLs. This approach keeps each web application in it’s own silo and avoids mixed mode. We create a Windows "Least Privileged" group to assign the web app user to as it’s only group, and in a perfect world the database access is granted for the web application user to specific stored procedures, views and functions rather than using the data reader, data writer roles. These application users are managed by server administrators not project developers. Complex, long passwords that are seldom changed, stored in off line encrypted file, and only available to server admins.
Developers are granted access to the database using own Windows domain credentials with restrictions on specific application database managed on development and QA servers by the one or two Project leaders. Production database access managed by DBA or Server Admins.
We wanted to set up for ease of development while maintaining as much security as possible between applications and on production. Avoiding mixed mode was also desirable. With non-IIS web applications we have to create the Windows user and provide a connection string in the application. Less desirable but sometimes required, when this is done that is one very restricted Windows user."
Kathy: "Jan 1999, I was a FORTRAN programmer. May 1999, I was asked to get involved with moving databases and applications from an old db system into SQL Server, but only 40% of my time. By Jan 2000, it was 100% of my time. There were no other database people. I was it. No-one to learn from.
I knew that management did not like the idea of "generic" accounts. So I created a SQL Server user account for each web application that I wrote. That account is given read/write privilege (for data) as well as execute privilege on the stored procedures I created for that application, but nothing more than that.
I went to great lengths to turn myself into a database programmer (even earning MCDBA). I wonder, though, if something happened to me: would anyone be able to maintain the code I have written? Does management understand the need for a *database* programmer instead of a FORTRAN or C programmer? Is there a better solution for web application security than what I have implemented? Is that solution more complex than a simple account for each application? If so, then would a non-database programmer understand it well enough to maintain it?
Some people within our organization want to have one-stop login that would provide appropriate access to both Unix-based as well as Microsoft-based web sites/applications. Is there such a solution? One that can be integrated with SQL Server security?
By the way, we don’t use Active Directory, just simple workgroup (lots of MAC and Unix/linux computers as well as PCs)."
More tomorrow – keep those emails coming!
Featured White Paper(s)
7 Steps to Successful SQL Server Auditing
This easy-to-read guide will explain and simplify the basic steps associated with successful SQL Server auditing & give you t… (read more)
BitLocker: Is It Really Secure?
What is BitLocker? How does it work? Is it a truly safe way to protect your data and applications, hard drive, and operation … (read more)