Today I am assisting a vendor with an upgrade / migration, as is very common in my work. I am amazed to still see the following practices in place with software vendors, even today, even after so many well-publicized data breaches. We’ve done what we can to mitigate, but the lax practices that were suggested, and that I keep seeing from ISV’s, still take my breath away:
- The vendor demands that we place an executable file for setup directly on the database server. In today’s world, most SQL Servers have been, or are being, consolidated. They are multi-purpose, complex machines. They are also generally mission critical. Why, when all database setup operations can reasonably be performed by the execution of a script from any workstation, would one design a system that demands a tech from outside the company run an executable directly on a database cluster, just to perform standard T-SQL database tasks?
- The vendor’s setup routines have ‘sa’ hard-coded into them as the only credential that can be used. This should be a no-brainer by this point: sa is simply a bad idea. In fact, we have a policy whereby sa is disabled everywhere because of the common vulnerabilities around it. How is this still happening today? Setup of a database for a vendor application should not even require the sysadmin role (how about dbcreator and maybe securityadmin?) much less a shared admin account/password combination provided to a person outside the organization.
What I do, and perhaps you can too, is this:
- Only provide such risky access or setup after explicit and specific request from the vendor, with an explanation of why it’s required.
- Isolate the damage, by performing setup like this on a separate, dedicated instance, then moving the data into production in a safe manner. Obviously, change the shared sa password immediately afterward.
- In a polite way, I complain to both the vendor representative and the project manager or client/business unit. Without complaint, and without a DBA pointing out this type of security risk, things are not likely to change. This kind of vendor often does not even know they should be embarrassed, so, without being rude, I embarrass them. I let them know what year it is, and what they should be doing.
Here are the best practices, I think, for an installer to set up a database for an application:
- The installer executable should run from a client machine, not be required to run locally at the server. Mr. ISV, there’s nothing you need to do at the SQL Server. Honest.
- The installer should ask for credentials, and should allow the technician to choose between performing the database setup under their current Windows account, a specified, non sa username and password, or, for bonus points, a different Windows domain account, such as an application service account. It’s really easy to do this. Very little effort is required. Not rocket science.
- The setup documentation should define what specific, non-administrative roles are required for the setup to run: generally dbcreator, occasionally securityadmin for the setup to establish its own logins. Sysadmin is practically never needed.