We all know the best practices for SQL Server service accounts–domain account (if you’re using Active Directory), non local admin, different one for each service (and server/instance), etc, etc. These are, of course, good best practices and they should be followed as closely as possible in Production and on servers/instances that house Production data.
A problem arises if you have more than just a couple-few servers or run some of the BI components, however. The number of service accounts involved in your SQL Server plant could be very large, necessitating an incredible amount of overhead when it comes to managing those accounts. This goes beyond simply creating and assigning them–chances are good that there are policies in place that require changing passwords. User accounts, service accounts, and other automation accounts likely all fall under this umbrella. If you’re lucky, maybe non-user accounts have a longer change interval, but it’s still something that is going to need to be done on a regular basis. In large environments, this could take an excruciatingly awful amount of time to do.
All of this is not to mention the human factor involved here. One of the recurring themes in a couple of my presentations is making an effort to automate as many things as possible to remove the human from the process. Not that we’re bad, but there are some things, especially tedious and repetitious tasks, where dumb things go wrong simply because of the nature of the work. Changing a bunch of service account passwords is definitely one of them. There used to be two types of sysadmins: those that have changed a service account’s password but forgot to update and restart the service itself, and those who will.
Enter Group Managed Service Accounts
Group Managed Service accounts (gMSAs) are a way to avoid most of the above work. They are special accounts that are created in Active Directory and can then be assigned as service accounts. They are completely managed by Active Directory, including their passwords. This means no more manual work to meet the password-changing policy–the machine takes care of that for you.
There are other security-related controls that can be gained with them, but this is the major feature.
I’ll also note that you–the DBA–are likely to need some help from your AD admin to get these set up. They’re going to need to actually create the accounts for you in the system, and there may be some changes needed to their AD configuration in order to support them. They’ll also need to have a Windows Server 2012 (or R2) domain controller in their domain, but I’d hope today that’s not going to be a hurdle to overcome.
Since I’m mostly here to talk about SQL Server, I’ll note a couple of different support situations. gMSAs are supported from SQL Server 2014 and on running on Windows Server 2012 R2 and on for everything you can do with SQL Server–standalone instances, Failover Clusters, Availability Groups. Just plain Managed Service Accounts (MSAs) can go back a little further, but they only support standalone instances of SQL Server.
From a non-SQL Server perspective, one of the major disadvantages of gMSAs is that one can’t just use them everywhere. Services have to be specifically designed to support the use of these accounts, and that’s not going to be the case everywhere.
Since this isn’t exactly a new feature, there’s plenty of documentation and blog posts out there to read about this feature and the various requirements to implement. There’s a great overview and setup blog post on MSDN here: https://blogs.msdn.microsoft.com/markweberblog/2016/05/25/group-managed-service-accounts-gmsa-and-sql-server-2016/
That post links to this old TechNet article, which still is a pretty good resource for understanding what these things are and a little more detail on what is going on in the back-end: https://technet.microsoft.com/en-us/library/hh831782.aspx
Finally, my coworker Joey has a slightly older writeup here, https://joeydantoni.com/2012/12/14/group-managed-service-accounts/, that walks through the process of setting this up. Note that some of the requirements have changed since that was written, but the general process remains the same.
gMSAs are a nice feature that aren’t too onerous to setup, but go a long way to make your life easier and your data far more secure.
With the announcement of the CPU “issues” in the last week or so, this week has quickly become Security Week at DCAC with our blogging. This week will all be capped off without security webcast this Friday. If you follow the DCAC Blog, you’ll see different security topics from everyone this week, with one new one coming out each week.
I wanted to take this time to talk about our old, and poorly named, friend SQL Injection. To date, this is still the most common way for data to be exposed. As applications get older and more corporate applications get abandoned the risk of them being abandoned gets worse and worse. As I’ve written about time and time and time again, SQL Injection is a problem that needs to be solved on the application side. However, with enterprise applications that get abandoned this becomes hard for a business to deal with as some business unit needs to pay for these changes.
And that need to paying for development time to fix security issues is why SQL Injection issues can come up. For old applications, business units don’t see a value in fixing applications (or at least verifying that there’s no issue with the application) so the applications will just sit there until an issue comes up. And by the time it does, those problems aren’t going to go away they’re just going to get worse as you now have customer data (or employee, or vendor, etc.) out there in the wild. Now you have a Public Relations issue on top of your security issue.
Issues like we saw this month get pretty logos and flashy names, but for the most part these kinds of issues require some sort of server access (yes I know there’s proof of concepts out there). But with SQL Injection as long as the application is exposed to users you have the potential for a problem.
We’re not just talking about external users here, but internal as well. Most breaches that companies have where data is taken are internal. In other words, you let the person into your office, gave them credentials to your network and let them go nuts on your network. I couldn’t tell you the number of unauthorized routers, Wi-Fi access points, or applications that scan the network I’ve found over the last 20 years.
So to recap, your biggest threats are employees that are inside your firewall, attacking old applications that haven’t been updated in years but still have access to information worth stealing.
It’s time to secure all those old applications.
The post Welcome to Security Week at DCAC appeared first on SQL Server with Mr. Denny.
Recently, I got to work with a client on something interesting. We implemented transactional replication to send data to an Azure virtual machine. This was being done to perform some testing for a project.
Given that the two machines were NOT within the same Active Directory domain, we wanted to make sure our client’s data was protected, so we utilized a Point-to-Site VPN to facilitate this. With the client using a VPN connection, this helps to ensure that any data transmitted to the virtual machine is encrypted and secured. Note, the process on how to configure and implement the VPN connection is for another blog post.
SQL Server replication requires the use of a server name rather than just the IP addresses. This meant that the virtual machine in Azure had to use an entry in the local host file that was pointed back to the client’s machine.
The down side? When the VPN connection drops (it happens), the client machine obtains a new IP address upon reconnecting. Potentially now the host file would have the incorrect IP address and needs to be updated. Not a difficult task but how do you know what to update it to? You could ask the client however asking the client to tell us what the new IP address every time, is well, irritating. Thankfully, there is a way to determine what the IP address might be from the Azure portal itself!
- Log into the Azure portal
- Go to Virtual Network Gateways
- Click on the Gateway that is using the VPN
- Go to “Point-to-site configuration”
On the subsequent blade, towards the bottom you’ll see the “Allocated IP Addresses”. Voila!
If you have multiple connections it might be more difficult to determine what IP address is for which connection. However, in this case, we only had a single connection so it is easy to determine. This allowed us to easily and quickly update the local host file without having to disturb the client for this information.
However, as easy as it is to determine the IP of the connection, point to site really shouldn’t be used for production. It was used in this case as a Proof of Concept (PoC) for a client. This was the fastest most secure way to connect Azure, even though it’s a bit fragile.
© 2018, John Morehouse. All rights reserved.
Awhile back, if you are on Twitter, you can probably recall my ranting about the 949 indexes I was reviewing. The process was to determine duplicate indexes and consolidate them or discard the unneeded ones. My ranting was not about the duplicates per se it was about the index names. It only takes a second to name an object with some name that tells what the thing is. Below I will show you some examples and give you an easy script that will help you generate your index names. Taking a little time to name things appropriately can go a long way, it can not only be time saving but can help to reduce redundancy.
As you can see from above, none of the names gave a complete indication of what the index encompassed. The first one did indicate it was a Non Clustered Index so that was good, but includes the date which to me is not needed. At least I knew it was not a Clustered Index. The second index did note it is a “Covering Index” which gave some indication that many columns could be included I also know it was created with the Data Tuning Advisor due to the dta prefix. The third index was also created with dta but it was left with the default dta naming convention. Like the first one I know the date it was created but instead of the word Cover2, I know there are 16 key columns are noted by the “K#” and regular numbers tell me those are included columns. However, I still have no idea exactly what these numbers denote without looking deeper into the index. The last index is closer to what I like however the name only tells me one column name when in fact it encompasses five key columns and two included columns.
Above we see a few good examples with varying naming conventions, but each tell me a much more than what we saw in the “Donts” list. The first one I know right away is a non-clustered index with two fields. The second is a clustered index with one field. The third is an index that has 9 fields, probably a covering index of some sort, which tells me that it is probably important to a specific query or procedure. Index four uses the name of the table and the field, which does give me more information but given the name of indexes are limited to 128 characters I prefer to leave that out. The last one closer to one of my favorites, because it does give more information. The name lets us know that it has an included column of Birthdate.
Here is the script I use when creating indexes. It will go thru and identify a missing index and create Index statement using a standard name convention.
NOTE: This modified version of what we use at DCAC is for just showing you how I include and create a standard statement in my code, this is not to be used to identify missing indexes, as it is not the purpose of my post. I have removed pieces of that from this script.
SELECT DB_NAME(c.database_id) as DatabaseName,
OBJECT_NAME(c.object_id, c.database_id) as TableName ,
c.equality_columns as EqualityColumns ,
c.inequality_columns as InequalityColumns ,
c.included_columns as IncludedColumns ,
'USE [' + DB_NAME(c.database_id) + '];
CREATE INDEX IDX_'
+ REPLACE(REPLACE(REPLACE(REPLACE(ISNULL(equality_columns, '')
+ ISNULL(c.inequality_columns, ''),
', ', '_'), '[', ''), ']', ''), ' ',
'') + ' ON [' + SCHEMA_NAME(d.schema_id) + '].['
+ OBJECT_NAME(c.object_id, c.database_id) + ']
(' + ISNULL(equality_columns, '')
+ CASE WHEN c.equality_columns IS NOT NULL
AND c.inequality_columns IS NOT NULL THEN ', '
END + ISNULL(c.inequality_columns, '') + ')
' + CASE WHEN included_columns IS NOT NULL
THEN 'INCLUDE (' + included_columns + ')'
END + '
WITH (FILLFACTOR=90, ONLINE=ON)' as CreateIndexStmt
FROM sys.dm_db_missing_index_group_stats a
JOIN sys.dm_db_missing_index_groups b ON a.group_handle = b.index_group_handle
JOIN sys.dm_db_missing_index_details c ON b.index_handle = c.index_handle
JOIN sys.objects d ON c.object_id = d.object_id
WHERE c.database_id = DB_ID()
ORDER BY DB_NAME(c.database_id) ,
OBJECT_NAME(c.object_id, c.database_id) ,
ISNULL(equality_columns, '') + ISNULL(c.inequality_columns, '') ;
Create Statement Output
USE [My_Reporting]; CREATE INDEX IDX_ID_StartTime_EndTime ON [dbo].[Shift]
([ID], [StartTime], [EndTime]) INCLUDE ([Notes], [EmployeeID]) WITH (FILLFACTOR=90, ONLINE=ON)
This statement gives the proper database context and create statement syntax, it adds all the needed key columns within the () and separated by commas. In addition, it adds the word INCLUDE and encompasses the included columns also in () and comma separated. Note the index name only includes the Key columns, which is just my preference.
Now everyone has their own naming conventions. You do you, however should stay consistent and give some meaning to it. When others look at the objects we should be able to know what it’s doing or be given a good clue as to what it’s for. This not only helps to quickly identity its definition but also keep you from creating duplicates. By looking at names you can tell with columns you need are already included in other indexes. Naturally you can’t just trust the name you have to dig deeper while examining your indexes but it at least will give you a realistic starting point.