6

I'm going to start off apologizing since this is my first post and looking at other questions they don't quite match my own situation, also I'm a SysAdmin, not a DBA so my understanding of SQL is rusty and limited.

My organization frequently encounters vendors who in order for their application to run they do what we refer to as lazy programming and request SA credentials or demand that their custom account has sysadmin rights. My organization also has data protection/compliance policies which require us to grant only required access and rights.

Most recently a vendor that I am working with wants their account to be a sysadmin, but it actually only needs access to a single database. If we give the AD account (because their application only works with Windows Authentication) sysadmin rights I can use the application's GUI to browse and select the database, but if we only make that same account the DBOwner we can't find anything.

Is there a way to find out if it is trying to poll other databases or determine what the lowest level of rights needed for the required database(s) would be? When I tried asking the vendor I was told, "It requires sysadmin rights, but its ok because it only accesses the one database." Which isn't ok when it comes to our policies.

Thanks for any advice.

  • Has this vendor supplied his own database, or they are using some of your databases? – McNets Jan 25 '17 at 18:16
  • 1
    Firing up profiler and then the app would help you see exactly what it's doing. – SQL_Underworld Jan 26 '17 at 5:37
  • 1
    Make the vendor sign an agreement that makes him liable for all consequences, intended or unintended. – Twinkles Jan 26 '17 at 7:53
  • Ask the vendor what specifically they need this for. If it is just needed in a few places it could be that either there is a permission that will do the same thing that they are not aware of, or perhaps Module Signing can be used to grant the elevated privilege for a specific set of procedures (which you will have to sign upon delivery, but then you get full control over what code is getting the permission, and the permission is lost if they change the code so you get to review the change). – Solomon Rutzky Jan 27 '17 at 16:48
1

If the vendor is providing their own database you could make their account apart of the owner role at the database level and all you need to do at the system level is grant the account login permissions to their database. This will allow them to have full control over their database and will prevent them from using the account to access other databases you have. If they tell you they need the sysadmin database role for tempdb access that is not true as every user has all the access they need to tempdb by default no other steps needed unless you made changes to the defaults for tempdb. Setting up their account this way will also prevent them from enabling other extended features in sql like mail and xp_cmd_shell which would give them access to the underlying OS.

There is other hardening you can do at the sql server service level to ensure that any sysadmin accounts in sql don't have admin rights on the OS. These include running the sql service and sql agent service as just a user on the box. Then adding the windows group mssql$....users to the Disk Maintenance and Log on as a batch policy. This will give the two accounts access to run as a service and will also allow sql server to do instant file initialization when it needs to grow the underlying data/log files. You will also need to grant full control at the NTFS level of the folders for the same group where the data and log files. Use the SQL Server configuration tool to change the accounts as it sets most of the needed permissions for you.

Here is a basic guide from MSDN while I try to find a more step by step on of the basics that I've used in the past

  • Thanks for the input. This is actually a 3rd party plugin service vendor trying to connect their online receipt service to our Microsoft Dynamics database, and based on my experience working with them, they probably have no clue what their system tries to talk to. We're a bit more security minded than a lot of organizations that I know and when a vendor says that they need unrestricted rights, we ask them to justify it. Sure it might be easier, but it isn't secure. – Mark Feb 1 '17 at 12:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.