DBAs: What Do You Do When Major Installations Require Sysadmin Access?

This is driving me absolutely crazy. For the 80th time, I’m required to create a user and give it the sysadmin role. I *have* to do this because the software is business critical (Blackberry Enterprise Server, Interwoven DMS) and installs will fail without it. With some software, I can temporarily grant access the take it away once the installation is complete. With others (such as SharePoint 2007) I can temporarily grant the account Database Creator and Security Admin roles then remove that once the install is complete. However, with Interwoven DMS, if I attempt to give the account *every* role except sa, the software will fail to do anything. Using SQL Profiler, I can see that the Interwoven logs in, checks to see if its sa and if not, then it quits. I filed a bug with the vendor months ago but have not heard back.

To make matters worse, Interwoven pretended that other DBAs were able to get around granting the user sa access but then were unable to help me duplicate their procedures. Later on, Interwoven admitted giving the user anything other than sa access would break the app.

So .. I can’t say no. All I can do is hope that their DB programmers are competent (which, if they require sa, are they really?) enough not to trash my servers or that filing bug reports will get the software vendors to fix the glaring security issues.

What do you do as a DBA when presented with this issue?

Chrissy is a PowerShell MVP who has worked in IT for nearly 20 years, and currently serves as a Sr. Database Engineer in Belgium. Always an avid scripter, she attended the Monad session at Microsoft’s Professional Developers Conference in Los Angeles back in 2005 and has worked and played with PowerShell ever since. Chrissy is currently pursuing an MS in Systems Engineering at Regis University and helps maintain RealCajunRecipes.com in her spare time. She holds a number of certifications, including those relating to SQL Server, SuSE Linux, SharePoint and network security. She recently became co-lead of the SQL PASS PowerShell Virtual Chapter. You can follow her on Twitter at @cl.

Posted in General, SQL Server
2 comments on “DBAs: What Do You Do When Major Installations Require Sysadmin Access?
  1. Rich says:

    After being burnt in similar ways, several times in the past, we now require that all software is approved by our IT Techical Design Team before purchasing.

    We have set out a list key Technical Acceptance Criteria that any software must meet before purchase is considered.

    One of the criteria, at least on the client side, is that the software must run under the context of a domain user and require no elevated priveleges. It is amazing how may software vendors still supply software that requires local administrative privileges. Software like that gets binned straight away.

    This has required a massive cultural shift in our organisation since individual departments were previously accustomed to just going out and buying a piece of software that they thought would work for them and installing it on their workstations.

    Now, however, we run a completely secure, locked down Terminal Services environment supporting 7000 users and all workstations have been replaced with Wyse Terminals.

    Departments must now apply to the IT Department with a business case for their software. Once this has been examined, two or three different vendors are considered and the software tested to ensure that they meet our Technical Acceptance Criteria. Only once this has been done is one vendor chosen and the software deployed on the Terminal Servers.

  2. Aaron says:

    I like Rich’s approach a lot and at my previous job we instituted a policy that IT could not and would not provide for or support any software which hadn’t been approved. At the new company it’s a little harder to make that fly as they’re still on a model of “We bought it, you implement it!” But we’re starting to change that mindset.

    What I’ve tried to do is split off the individual applications that I consider to be the most problemmatic onto their own instances. Luckily I’ve only found a few, and honestly when I actually push the support people I can usually get different answers. For instance we were setting up the Blackberry Enterprise management suite and they wanted the account it installed/ran under to have sa. After about 45 minutes on the phone I was able to get them to admit that it really only needed dbo to their primary database and execute permissions on a few other procedures. Sysadmin rights were only required during installation and could be backed off later. That’s a little more than I’m used to (I’m not against giving a service db_creator) but at least that was a reasonable compromise… however it’s not documented at all in the docs we were sent with the product.

    We also had the management system for our point-of-sale systems at the last job (the casino) which had numerous sql accounts created with blank passwords, etc… When I did a security evaluation of that box it raised some serious red flags. It took about four hours on the phone with their support and some threats but eventually they sent us an internal tool that would enable us to change how the various services they had connected to SQL and then we could set strong passwords.

    Though at other times… their design just sucks.

Leave a Reply

Your email address will not be published. Required fields are marked *

*