olly - Fotolia
In my book, Learn SQL Server Administration in a Month of Lunches, I tried to explain to new SQL Server administrators how SQL Server security is supposed to work. I also had to be honest with the reader, admitting that very few applications are actually written to use SQL Server security the way it's intended. I blamed the problem on lazy database developers -- an assertion I stick by. Not surprisingly, one or two of the book's early reviewers were developers, and took exception to my comment. "SQL Server isn't the only thing out there," one wrote. "I have to write applications that will work with Oracle, too." In other words, instead of taking each database platform's unique security into account, he's just going to go with the lowest common denominator because it's easy, and he doesn't care about the impact on the customers.
You need to be aware of this attitude on the part of database developers and vendors -- and you need to fight it.
The lowest common denominator database security technique is to create only one user account in the database, and then have every copy of the application log in using that single user account. The application then implements its own "security," which I'm sure is every bit as robust as the platform security engineered by hundreds of experts at Microsoft, Oracle and other platform companies.
The problem with the approach manifests in several important ways:
Auditing: When only one user account accesses the data, you can't tell who is doing what. Sure, the application can implement its own audit trail, but that could by bypassed by any knowledgeable user who has the account's username and password.
Security: Embedding the username and password into a connection string makes it trivial for anyone to discover that information. Readily available utilities can decompile common corporate applications in seconds, leaving the connection string -- and its password -- in clear text for anyone to see.
Troubleshooting: Since you can't pin an individual database connection to a human being, it's tough to troubleshoot performance and operational problems. When one user complains that something is running slow, the admin can't tell who's who on the server side of things. This means problems persist longer than they need to.
It would require almost zero effort for a developer to make his application use SQL Server's native security instead. All it takes is a change to the main connection string -- removing a username and password and letting the actual human being's Windows login credentials pass through. In most environments, that's all the database administrator needs to do, aside from granting some server-side permissions so those humans can read and write the necessary data. You'll get to take advantage of SQL Server's own auditing features, better troubleshooting and the increased security that comes from not having a clear-text password for an all-powerful user embedded within every copy of your applications.
So how do you fight the lowest common denominator database security technique? It's simple. Establish purchasing criteria. Make sure new applications use SQL Server's Windows authentication mechanism and nothing else. Convert existing applications. You may need to work out a timeline with your vendors or let them know the lucrative stream of revenue called "maintenance fees" is in jeopardy. Take control of your environment – don't hand your company over to someone who has decided the best security for you is whatever is easiest for them.