Security management in SQL Server is far more than just creating a login or two: it is a full lifecycle of preparing for and passing audits, managing user accounts, and setting policies.  SQL Server itself comes with few mechanisms for streamlining the security lifecycle, and so a database administrator can spend a very large percentage of any given day solely on security tasks.  The time spent can become truly breathtaking in medium and large shops, where an audit can take weeks of full-time preparation. And still, it’s easy to miss important issues.

The Minion Enterprise security modules solve these security lifecycle issues – and many more – across your entire enterprise. Called “ME” for short, Minion Enterprise is a SQL Server management solution. It collects extensive data about each managed server, including SQL Server security data and Active Directory security information, to a central repository.  That single data repository allows ME to provide centralized auditing, investigation, configuration, reporting, and alerting, which drastically reduces the time spent on difficult and tedious tasks. Whenever possible, Minion Enterprise also automates tasks.

The following sections outline just a few of the security services that Minion Enterprise contributes to a SQL shop, including:

· Collecting SQL Server and Active Directory security data into a central database.

· Researching the AD permission chains for a single user.

· Researching high level permissions (like sysadmin) for all the instances in an enterprise.

· Alerting on changes to server level roles, across all SQL Server instances.

· Cloning account permissions to additional users, to Windows groups, or to SQL roles.

Research Account Permission Chains

SQL Server DBAs typically have very little insight into the membership of Windows groups that have SQL Server permissions.  To research group membership for security or for an audit, a DBA must gather a list of Windows groups from the server in the environment, submit a request to another team, and wait for the response – which is almost always in an impractical format. And then, she must do it all again for the next server, dozen servers, or 200 servers in the shop. If she wants to maintain security over time, she must repeat this process as often as every week. As you may imagine – or know personally – this takes a massive amount of time.

On installation, Minion Enterprise automatically begins collecting Active Directory information and SQL Server security data to tables in the central repository. Use the ME Active Directory expansion feature to discover Windows groups with access to SQL Server, and all of the users in those groups and subgroups, no matter how far down they’re nested.  What’s more, this module displays the chain of inheritance for users that have several levels of nested Windows groups.

The screenshot above is of the User Chain report – one of the many reports available with Minion Enterprise. The User Chain report allows you to select a particular user, and discover all of the SQL Server instances that he or she has access to. And, it shows you the paths through which the user is granted permissions.

The User Chain report displays:

· the name, internal instance ID number, and service level* of the managed instance

· the name of the top level Windows login that has permissions to SQL Server (for this report, the Windows login is the Active Directory group that is directly granted SQL permissions)

· the individual member account that has permissions to SQL Server via that top-level Windows login

· the chain of Active Directory inheritance between that top level login and the individual login

· the number of levels (nested Windows groups) between the top level login (the Windows group) and the AD login

This report is a convenient way to investigate user permissions. Additionally, the data underlying this report is stored in tables in the ME repository, ready for any query the DBA would like to put together. You can query for one user’s SQL accounts, Windows account, and all group memberships to determine what permissions he or she really has, on which servers, and how.

*Note that when you add a server to ME, you assign it a service level of Gold, Silver, or Bronze. You can then reference lists of servers, or service levels (among other options), in order to manage specific groups of servers in Minion Enterprise.

Discover who has Sysadmin across the Enterprise

We have already seen how you can use the provided reports to discover the chain of inheritance for a user – not just for one server, but for all instances in your environment. We can also approach security research from the other side. It is a simple matter, for example, for the Active Directory expansion module to find all of the users that have sysadmin rights, on one server or on many.

“Sysadmins per Instance” is a high level security report. It allows you to choose what set of servers to investigate, and then it displays a summary bar chart of SQL accounts that are members of the sysadmin security group, followed by a table that provides detailed information. The bar chart at the top of the report is colored to show the method of entry: on the SQLDev1 server, for example, four Windows users were directly given sysadmin; seven Windows users obtained sysadmin rights via Windows groups; and four SQL logins were directly given sysadmin rights.

The details table at the bottom of the report breaks out all of the specific accounts that are members of the sysadmin security group, on each instance. “Login Type” refers to the method by which the account obtained sysadmin rights. For example, “RemoteUser” is granted sysadmin privileges directly, as a SQL login.

This report is immensely useful, and makes sysadmin rights visible across a single server, a group of instances, or all instances in your environment.  And of course, Minion Enterprise provides other reports, procedures, views, and the underlying tables themselves to simplify many other kinds of discovery. 

Audit an Enterprise using T-SQL

Throughout our years as full time DBAs and consultants, we have often stressed the importance of “living in audit mode”. An audit can come at any time, and an IT group rarely has much advanced warning. They often have difficulty obtaining some of the data, and it can take days to meet audit requirements. Simply everything – development, production, and so on – is put on hold for the audit.

Living in audit mode not only prepares you for these surprise inspections, but also keeps a SQL environment well maintained. Some audits require you to show that audit controls have been in place for a matter of months! ME allows you to meet that requirement, too.

The central data repository in Minion Enterprise simplifies and streamlines a majority of this work. With simple queries, you can determine which accounts:

· are disabled

· have not logged in within the last six months

· have weak passwords

· have a high number of failed login attempts

· have been created or dropped recently

· have elevated permissions

· have not changed passwords recently

For example, the query to pull back a list of sysadmin accounts for a single server is:

SELECT InstanceID, ServerName, Name, sysadmin, LoginType

FROM Collector.LoginsCurrent

WHERE sysadmin = 1

    AND ServerName = ‘MINIONCON’;

If you’d like a list of the sysadmin accounts across all servers, just remove “AND ServerName = ‘MINIONCON’”.

This transparency and effortless access is revolutionary in SQL Server security.  You can discover security issues, perform audit pre-checks to make sure you’re going to be compliant, and pass audits with a minimum amount of time spent – whether you’re auditing 2 servers or 2,000.

Another example of this transparency is the Password Policy Enforcement report, which gives a breakdown per server of the count and names of logins, and their password enforcement status:

For more examples of security research using Minion Enterprise, see the video “Minion Enterprise: Active Directory Group Expansion” on www.YouTube.com/MidnightDBA.

Alert on Changes to Server Level Roles

Particularly in shops that use Windows groups to manage SQL security, a database professional needs to know who is coming and going out of the server level roles. Minion Enterprise automatically sends an alert when users are added to or removed from one of these roles.  If a top level Windows group is added to an administrator role – even if the account is buried many groups deep in Active Directory – ME will fire an alert.

Companies commonly have a global DBA group, which has high level permissions to most or all of the SQL Server instances in the enterprise. This is one prime example where server role alerting is critical. If, for example, the accounting group is accidentally added to the global DBA group, it is imperative that you find out about the elevated privileges right away!

Naturally, you can create additional alerts for any type of change, because the raw data is available in the central ME repository. For some companies, this may mean alerting on any new user on critical servers. Or, it might mean adding the accounts of former employees to an alert list. Minion Enterprise’s data collections provide an enormous amount of flexibility.

Manage Security with User Cloning

We’ve talked about research and alerting, and now we’re down to managing security. ME provides a wealth of security management features, including weak password detection, orphan prevention via the SID server, and reporting on SQL account password policy enforcement. But one of our especially favorite features is user cloning.

Say that DBA Dave has access to one SQL Server instance. You want the new DBA, Sarah, to have the exact same rights as Dave. Traditionally, this means looking at all the server level, database level, and object level rights that Dave has on that server, and giving Sarah rights to the same groups, databases, and objects. But if Dave has rights to 67 databases on that instance – or individual object permissions on several databases – we are in for a very long day.  What could be even worse? If Sarah needs permissions like Dave’s on not one, but on 200 SQL Server instances.

This is exactly where Minion Enterprise shines the brightest. User cloning makes Sarah’s permissions look exactly like Dave’s across the entire enterprise, and it’s just as effortless on 200 servers as it is on one. ME scripts and pushes all of those changes for you.

What’s more, ME gives you the power to add server-wide user cloning to your processes when you set up new instances, such as test and staging servers. For example, when you set up a new test server, you can clone all user permissions – for all users – from the production server, to the new one! Because user cloning is a stored procedure, you can add the execution call to your processes.

That brings the security lifecycle full circle: from discovery and auditing, to alerting, and user setup and management.  And we haven’t even begun to cover all of the functionality of Minion Enterprise security.  But you’ll have plenty of time to unearth the rest with a 90 day trial license! Download your copy of ME today from www.MinionWare.net, and work like a DBA.