When you use a relational database engine for an application, where shared credentials are used to gain access, it is very important how those shared credentials are configured. We’ll take a little look into why.
I have consulted with a few companies that used this approach of a single login for database access. Oftentimes, they used the SA account, or a machine, or domain account with the SA equivalent. One such company had a web application, hosted completely on a single server, with no DMZ. I remember the log files getting very large one weekend. On Monday, I was looking at the log files, and found millions of hacker attempts to get into the database using the SA account.
I keep reading that the majority of application hacks come from inside resources. I have not be able to confirm this is true from any documented study; I just keep reading it. Let’s say there is some truth to this theorem. Then, it is not only the applications exposed to the internet that are vulnerable. Even your internal, LAN based applications, are at risk from hackers. Again, using the SA equivalent is a risky proposition.
There is a single solution that solves this problem: “Don’t use SA, or any Administrator password, for application access to your database.” What should you do instead? You need to create an account for you application to connect to the database. It needs to be restricted to only those permissions necessary to make the application work. I’ll talk about that a little more in a later editorial.
Following is a table of implementation strategies you can use, depending on the availability of Active Directory for the database server, the application server (if there is one), and the application user.
| Database | Application | User | Implementation |
| No | No | No | Combine Authentication and Authorization
Create a local account for the user on the databas instance per user, and restrict permissions through schema or roles Separate Authentication and Authorization Create a s local account on the database for the application, using rules or schema to restrict permissions implement your own User authentication in your application through local accounts or using your own rules/rights management implementation |
| Yes | Yes | No | Separate Authentication and Authorization Create a Domain Account for Application to Database authentication and permissions Implement your own user authentication to the application using your own rules/rights management implmentation |
| Yes | Yes | Yes | Combine Authentication and Authorization
Create a Domain account for the user on the databas instance per user, and restrict permissions through schema or roles Separate Authentication and Authorization Create a Domain account for the application on the databas instance per user, and restrict permissions through schema or roles implement your own User authentication in your applicatiom through Active Directory Roles |
There are other permutations of what is available and how to implement authentication and authorization. The ones I have listed are the most common, in my experience.
There is nothing earthshattering in this. I keep running into companies with risky implementations, and found it important to refresh our memory. Start your development with a Security and Access strategy in mind, and this won’t become a problem.
Cheers,
Ben