THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Merrill Aldrich

It’s 2011: Do you know where your SA credentials are?

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:

  1. 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?
  2. 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:

  1. Only provide such risky access or setup after explicit and specific request from the vendor, with an explanation of why it’s required.
  2. 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.
  3. 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.

Instead

Here are the best practices, I think, for an installer to set up a database for an application:

  1. 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.
  2. 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.
  3. 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.
Published Tuesday, July 12, 2011 2:44 PM by merrillaldrich

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Davide Mauri said:

I like the option 3. Vendor must understand that as DBA is our duty check that security is not compromised in any way, ever. No way I'm going to enable "sa" anymore.

July 12, 2011 6:17 PM
 

Josh Feierman said:

Couldn't agree more here. I hate it when vendors are lazy and just demand blanket sysadmin rights. It's even more inexcusable to demand THE sa account.

I've sworn in front of people that if I ever own an ISV my software will make administrators (as well as business people) smile, not cringe.

July 12, 2011 7:15 PM
 

Peter said:

Have a look at Microsoft Dynamics CRM which needs the sysadmin role and to be in the local administrators group on the database server while a new organization database is created.

July 13, 2011 4:36 AM
 

eccentricDBA said:

I agree. My major issue is the number of products produced by Microsoft that does this. It would be great if someone would start a website that would document that rights required for these applications or at least list the applications the applications that either do things right or wrong.  It would help when performing application selection.

Honestly, I would be a fan of saying any application that is "black" listed it is automatically removed during the application selection process.

July 13, 2011 8:44 AM
 

merrillaldrich said:

@Peter - I know, right? I've done that one. MS is also an offender, though less often.

July 13, 2011 6:33 PM
 

magasvs said:

Another Microsoft application that requires sysadmin role is SCCM.

July 17, 2011 11:49 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement