September 20, 2015 at 1:41 pm
I am STIGing my SQL instance and ran across an issue. Any help is appreciated.
There is a STIG Check that does not allow grant "Connect SQL" directly to any logins except SQL System and the SA account.
My way of resolving this is to do the following:
Step One:
We create a Server Role called SQL_APPLICATIONS – for the application accounts
We create a Server Role called SQL_DBA – for the DBA accounts
and give them direct “Connect SQL’ server permissions. MAKE THE ROLE OWNER = sysadmin (group)
Note: I think that creating a Server Role is only available starting with SQL Server 2012, but not sure. I am using SQL 2012
Step two:
I add the members (Logins - SQL & Windows) – in this case any application accounts and DBA accounts to the new roles respectively
Step three:
I remove the “Connect SQL” Permission from each Login
The first problem i noticed is that the maintenance plans failed with "The owner domain\username of job db_backup does not have server access.
I am currently using a test system and wondered If you think I will have trouble with the application connecting when I try and implement on the production systems.
Any help is appreciated.
Jeff
September 20, 2015 at 3:47 pm
That is an interesting requirement for sure. Anyway, what login owns the maintenance plan job(s) that failed, and is it part of one of those two groups that grants it connect access? I think as long as all the accounts used for your applications are part of those server roles you should be fine. Though, when you say you remove the connect permission from the logins, are you doing a REVOKE or a DENY? When I played around with this scenario, the DENY overrode the role's permissions and the login couldn't connect.
Senior DBA - Gateway Ticketing Systems
Co-Founder - Do It Simply Software
September 21, 2015 at 6:37 pm
Thank you very much for the response.
These are the answers to your questions.
what login owns the maintenance plan job(s) that failed: My windows account in SQL and I am
part of the sysadmin role as well
Is it part of one of those two groups that grants it connect access? Yes, both
Though, when you say you remove the connect permission from the logins, are
you doing a REVOKE or a DENY?
I go into the properties of the account - securable - I unchecked grant. I close and open the properties of the
account again and there is nothing in Securable any longer which is ok.
When I played around with this scenario, the DENY overrode the role's
permissions and the login couldn't connect. I see that is interesting.
This is part of the DoD Security Technical Implementation Guide (STIG) and I am working at understanding all the checks and how to mitigate the findings.
Your help is appreciated.
Jeff
September 21, 2015 at 6:47 pm
I figured out a way to test on both dev or production:
I will restore a database from an existing database and call it TEST_DB. I will also create a new SQL login called TEST_Login and create the server roles and add the members as mentioned earlier.
I can test in two ways.
A: I can log in I with TEST_Login and see if I have access and can modify the obects in the TEST_DB
B: I can ask the developers to connect the application to the TEST_DB using TEST_Login and see if the application works OK. Its nice having developers working with you on the same team.
I will post my results
Once i finish this part I have to do the same thing with the Database Roles. Again no direct access.
Jeff
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply