Thursday, April 10, 2014

SQLSaturday #297

SQLSaturday #297 in Colorado Springs is this coming Saturday! If you haven't registered yet, why not? It's a free event that has the best SQL Server professionals traveling from all over to present and attend this awesome event. If you able to make into Colorado Springs by Saturday, you should be at the event! I will be presenting "Capture Change and Apply it With Change Data Capture & SSIS" at 3:15pm (keep your eyes on the schedule page or use the Guidebook app to track any changes to the schedule). I'm looking forward to seeing everyone this Saturday, make sure you register and support all of the sponsors of this event when you are there.

Below are some more details from Chris Shaw's blog about the food drive that is also being done as part of the event:
What we are asking is that when you come to the event on April 12th that you bring with you food items that you would like to donate the more items that you bring the better your odds are in taking home one of these awesome SWAG items. We will soon be sending everyone who is signed up more details on individuals can participate in our food drive. You don’t have to bring any food items to be registered for the giveaways and there will be opportunities to participate in our Data-Rodeo for additional chances to win. Why a Data-Rodeo you ask? This year’s theme in going to be centered around the old west, our event will be taking place in a historical building that is over 100 years old, in the heart of the oldest part of town.
See y'all on Saturday!

Wednesday, April 2, 2014

SSDT-BI for Visual Studio 2013

Microsoft quietly released SSDT-BI for Visual Studio 2013 as a separate download a couple of days ago: No blog post or anything yet that goes through all of the features, so my assumption is it just adds the same functions that SSDT-BI for Visual Studio 2012 does for the latest version of Visual Studio (at least until later this year when Visual Studio 2014 is released). I also assume that this adds support for SQL Server 2014 BI, same as the previous updates for SSDT have done now for Visual Studio 2012 and 2013.

Just for review, SSDT-BI is for SSAS, SSIS and SSRS development. For database projects, they have now built that functionality back into all versions of Visual Studio 2013 (available as a separate download for Visual Studio 2012) and can be updated via Visual Studio 2013 directly. For more updates on SSDT, make sure you follow the SQL Server Data Tools Team blog.

Update: At about the same time I posted this Kasper de Jonge posted about SSDT-BI for Visual Studio 2013. Biggest disappointment is that there is NO backwards compatibility with SSIS 2012 or SSIS 2008R2 projects, so if you are only doing SSIS development on previous versions of SQL Server, don't jump on this upgrade just yet. No explanation was given for this incompatibility or if it will be fixed in a future update. Just keeps getting more and more complicated.

Wednesday, March 26, 2014

SSDT SQL Server 2014 Update

Microsoft has announced that a new version of SSDT for Visual Studio 2012 is now available for download and that an update for Visual Studio 2013 will be released through the update tools built into VS 2013. This new version adds support for SQL Server 2014, which will be available next Tuesday (April 1st). Along with support for SQL Server 2014 there are some new features in SSDT, including:

  • Filtering Data when using View Data - filter the rows shown and even hide columns along with applying aliases to columns and sorting the data by columns
  • Azure Integration - Add and connect to Azure databases without needing to setup firewall rules or other complicated network setup
  • Change T-SQL Query Connections - Change the database that the query window is current attached to so that you can execute the same query against another database without having to disconnect and connect

There are few other new features that I have not mentioned, make to check out the SQL Server Data Tools Team blog for more details!

This release is for the data tools portion of SSDT only, SSDT-BI for SSAS, SSIS and SSRS development will be released separately at a later date.

Tuesday, March 25, 2014

Encryption in SQL Server (Part 4)

Finally, the last part in my Encryption in SQL Server series that I started at the end of 2013! If you have not been following along, please see Part 1, Part 2 and Part 3 before reading this post.

Now that you have data in your databases encrypted and hopefully you have automatic backups setup against these databases, you are probably wondering how you can restore these backups while maintaining the encryption. If you are restoring to the same server and you have not made any changes to the Service Master Key (SMK) it will all work seamlessly.

If however you are restoring the backup or attaching a database with encryption to another server that does not have the same SMK you will need to take a couple of extra steps to get the encrypted data visible and encrypt new data working. Keep in mind that the SMK is the key that is generated at the time the SQL Server instance is installed on the server and you have no control over how that key is created. This SMK is used as part of the whole encryption setup on the server, so you may think that all is lost, but Microsoft has put in place some commands that will still allow you to access the encrypted data and encrypt new data into that restored/attached database.

After restoring/attaching the database to the new instance, run the commands below in order.

Restore Service Master Key (SMK), but only if this instance has no other encryption currently setup on it. If you do have encryption already on this instance, just skip this step and move on to restoring the DMK and modifying it to use the SMK (based on backup sample shown in Part 1):
    FROM FILE = 'C:\localhost_SMK.smk'
    DECRYPTION BY PASSWORD = 'ADa329wopkj*&ER.slkqksl';
 Restore the Database Master Key (DMK):
RESTORE MASTER KEY FROM FILE = 'C:\localhost_AdventureWorks_DMK.dmk'             DECRYPTION BY PASSWORD = 'U982LKJOWlkslpq&^@#lskjnkxOPx.w'                     ENCRYPTION BY PASSWORD = 'Ils20*(LKjqwnslqo372,cklweLKHJn'
Next we want to alter the DMK to use the SMK for encryption, otherwise the DMK will have to opened every time we want to do any encryption/decryption:
    DECRYPTION BY PASSWORD = 'Ils20*(LKjqwnslqo372,cklweLKHJn';
Now we need to create the Asymmetric and Symmetric Keys that we created previously in Part 2, in the same way they were created previously as we could not back up these since they were not created in certificates (if you did decide to create these keys with certificates and you have the back ups, then you can just restore the certificates):
 (SELECT * FROM sys.asymmetric_keys WHERE [name] = 'AdventureWorks_AsymKey')
    (SELECT * FROM sys.symmetric_keys WHERE [name] = 'AdventureWorks_SymKey')
    CREATE SYMMETRIC KEY AdventureWorks_SymKey
        WITH KEY_SOURCE = 'I82xskoiw820KOW>282kxow',
            ALGORITHM = AES_256,
            IDENTITY_VALUE = 'w,xi292XWE82S92iqoxl*&23'
        ENCRYPTION BY ASYMMETRIC KEY AdventureWorks_AsymKey; 
It is important that you use the same KEY_SOURCE and IDENTITY_VALUE when re-creating the Symmetric Key as this and the Asymmetric Key were used to do the initial encryption of the back-up and if these values are not the same the decryption of the previous data will not work.
Once all of these steps are complete you will be able to use the same commands we used in Part 3 to verify that the decryption works correctly:
SELECT Phone_encrypt, Phone,
CONVERT(nvarchar(25),DecryptByKeyAutoAsymKey(AsymKey_ID('AdventureWorks_AsymKey'), NULL, Phone_encrypt, 0)) AS 'CustomerPhone_decrypted' FROM Person.Contact;
SELECT Phone_encrypt, Phone, CONVERT(nvarchar(25),DecryptByKey(Phone_encrypt))) AS 'CustomerPhone_decrypted' FROM Customer.Contact; 
CLOSE SYMMETRIC KEY AdventureWorks_SymKey;
With these steps and the previous posts in this series you should be able to take full advantage of encryption in SQL Server and have full control of that encrypted data on any of your servers/instances. With all of the data breaches that are occurring today it is even more important for you to make sure any sensitive information you have in your database is protected and this is a great way to do it in SQL Server without causing a major impact to you existing systems.

Tuesday, March 18, 2014

SQL Server 2014 RTM Announced

Microsoft officially announced today on the SQL Server Blog that SQL Server 2014 will be released on April 1st! Always find it funny that Microsoft will release products on April Fools Day, but to them I guess it is just the beginning of another month and quarter for them. For those that are just getting upgraded to SQL Server 2012, now you will already be one version down after April 1st.

In past years this release would have been called SQL Server 2012 R2, but thankfully they have decided to drop the R2 (Release 2) naming on SQL Server products (it is still being used on Windows Server and other server products). See the official blog entry for a list of the new features, mostly just enhancements to the features added in SQL Server 2012. The best new feature that I see is the enhancement to In-Memory ColumnStore Indexes making them updatable! There is also the feature of being able to backup right to Azure, which is great for providing instant off-site backups for your databases.

If you want to be notified of the official release, click here and fill out the form.

Tuesday, March 11, 2014

Upcoming SQLSaturdays

Sorry for the lack of posts over the last few weeks, it has been very busy, but I will get the last post in the encryption series up very soon.

Wanted to spread the word about a few SQLSaturdays that are coming up, which I will be at!

SQLSaturday #295 - Las Vegas, NV (4/5/14)

This is the first SQLSaturday in Las Vegas and while I did not get one my sessions selected to present, I will still be in Vegas that weekend thru Wednesday (4/9/14). Was a great excuse to take some time to visit Vegas, which I haven't been back to in around 10 years. Looking forward to seeing how the Vegas crew puts on a first time event and meet up with all of the #SQLFamily in a very fun city! If you are going to be at SQLSaturday #295, I'll see you there!

SQLSaturday #297 - Colorado Springs, CO (4/12/14)

Glad to see the Colorado Springs SQLSaturday back on the schedule again after taking a break for a year. Also very happy to be presenting at the event again this year. I'll be presenting my CDC with SSIS 2012 session "Capture Change and Apply It With Change Data Capture & SSIS" later in the afternoon (schedule is always possible to change, so make sure to check beforehand for exact time and room). The organizers are going to do a food drive at this event as well, to help out the local food banks which have been very stressed after all of the wild fires last year. The group down in the Springs always puts on a great event, so please register to attend this event if you are going to be in the Colorado Springs area on 4/12!

SQLSaturday #285 - Atlanta, GA (5/3/14)

The Atlanta SQLSaturdays are typically the biggest in the country (maybe even the world), so I'm very honored to be speaking at this event for the first time! Looking forward to spending some time in Atlanta, instead of just transferring from one flight to another at the airport. I'll be presenting my review of the new features in SSIS 2012 session "SSIS 2012: More Than Just a Pretty UI" in the afternoon. As mentioned above, make sure to check the schedule on this page to verify when and what room I'll be presenting in. If you are going to be in Atlanta on 5/3, register for this great event with over 50 sessions!

Best thing about all 3 of these events, they are all FREE! These events are held throughout the world and put together by community volunteers that want to share with everyone the SQL Server technology that we all love. Also be sure to talk to the sponsors at each event as it would be impossible to put on these events without their support. For those in the Denver area, we are working on the announcement for our 4th SQLSaturday in the coming weeks!

Friday, February 14, 2014

PASS BI Virtual Chapter Presentation February 2014

On Thursday, February 13th I had the pleasure to present my "Master the Date Dimension Like a Time Lord" presentation for the PASS Business Intelligence Virtual Chapter. Was a great group of attendees that had lots of questions, which hopefully I was able to answer to their satisfaction. Also had a few attendees that mentioned they were also big Doctor Who fans as well, which always makes the presentation fun to do when there are those that get the references to the show.

I have presented this session multiple times before, but I keep changing it as I go based on comments I receive. So, since the content changes, below is the link to download the latest version of the slides and the script to build the Data Dimension table as I demo in the presentation.

If you come up with something that you think is really cool added to this script, please send it to me and I can pass on your updates to others as I present this again in the future.

Thanks again to all of those that attended. If you missed the session there should be a recording of the session available soon, and as soon as I get word that it is posted I will update this post with that link.

3/1/2014 - Recording of the session has been posted on the PASS DW/BI VC YouTube page.

Monday, January 27, 2014

SQLSaturday #271 Albuquerque

Saturday I had the privilege to present a session at SQLSaturday #271 in Albuquerque, NM. This was the 2nd year in a row that I have presented at the SQLSaturday in Albuquerque and I love going back each time because Keith Tate, Meredith Ryan and the whole ABQSQL crew do a great job putting on this event. This year the event was in a different location that was pretty amazing looking and the spaces they made available were great too. My session was at the very beginning of the day at 8am, so I had to make sure I didn't party too much the night before at the Speaker/Volunteer party. Speaking of which, that party was held at a local micro brewery and we even had a room that with a glass wall so that we could see all of the beer vats. Beer was great and I even shared this with my wife and son who traveling with me to this event to make it into a mini-vacation from Thursday - Sunday.

Was awesome to spend a few days in ABQ this time as I drove in for last year's event on Friday and left right as the event finished on Saturday. This time with the family we were able to go up to the Sandia Peak Tramway, visit Old Town Albuquerque and Petroglyph National Monument. All great places to visit and always nice to learn about another area of the country!

Back to the SQLSaturday event itself, things started a bit slow (it is a Saturday morning so this is expected), but quickly there were over 100 attendees going to sessions throughout the day. Since my session was first thing I expected that I would have a lower attendance, and while I only had 10 attendees in the room they all commented to me afterwards that they enjoyed it and learned something new, so that makes it all worthwhile! Here is a link to download the file that all of the content from my "Master the Date Dimension Like a Time Lord" session. For those that were not at SQLSaturday #271 and may have seen this presentation in the past, I have updated it to make the script to generate the date dimension for both SQL 2008 and SQL 2012. I also took sometime to clean up some of the code to be more SQL centric instead of Oracle that it was originally written for. I will continue to tweak this script and the presentation over time, so keep your eyes here for future updates to the scripts!

Thanks again to the Albuquerque SQL group and I look forward to coming back again in the future!

Thursday, January 23, 2014

Encryption in SQL Server (Part 3)

Now that we have the database setup for encryption as shown in Parts 1 and 2 of this series, it is time to actually encrypt the data in the database and view the data in decrypted form.

The great thing about all of these encryption capabilities being built into the SQL Server engine is that the commands that you use to encrypt/decrypt the data is very easy, in fact it is using the same commands you are already using to with some minor differences.

Before updating the data a new column that is in VARBINARY datatype will be required, since that is the datatype required for encrypted values:
ALTER TABLE Person.Contact
   ADD Phone_encrypt VARBINARY(128);
The length of the VARBINARY column will vary based on the length of the data you are encrypting and the type of algorithm being used. To find out what length of VARBINARY will work best for you, it is a good idea to setup a test where you populate the maximum length value of the column you are going to encrypt and do the encryption into a VARBINARY(MAX) column and use the DATALENGTH function to find out what the length of encrypted data comes to. Hopefully you are not encrypting BLOB data or other very large columns, in most cases a VARBINARY(128) or VARBINARY(256) will cover you, but setup the test as described if you have any doubts as you don't want to truncate the encrypted data.

The command to populate the new encrypted value column is a simple UPDATE command that you are already used to:
UPDATE Person.Contact SET Phone_encrypt = EncryptByKey(Key_GUID('AdventureWorks_SymKey'), Phone);

CLOSE SYMMETRIC KEY AdventureWorks_SymKey;
This will replace the values that are in the column specified in the UPDATE command with the encrypted version of that value using the Symmetric Key. Depending on the amount of data in your table and the specs of the computer/server this operation should not take very long to complete, in most cases no longer then it takes to perform that same UPDATE command without the encryption.

Notice the OPEN and CLOSE commands that are wrapped around the UPDATE, those are required as you need to make the Symmetric Key that you are using for encryption available and this is done by opening it. For good practices and security is best to then close the key after you have completed the work that you need to do.

Now that you have encrypted the data in that column, lets decrypt the data and see if comes back as the same value that you originally encrypted.

There are a couple of different ways to write the SELECT statement that does the decryption, first is using the Asymmetric Key directly:
SELECT Phone_encrypt, Phone,
CONVERT(nvarchar(25),DecryptByKeyAutoAsymKey(AsymKey_ID('AdventureWorks_AsymKey'), NULL, Phone_encrypt, 0)) AS 'CustomerPhone_decrypted' FROM Person.Contact;
And the other way is to open the Symmetric Key as was done above to perform the UPDATE and use it to do the decryption:
SELECT Phone_encrypt, Phone, CONVERT(nvarchar(25),DecryptByKey(Phone_encrypt))) AS 'CustomerPhone_decrypted' FROM Customer.Contact;
CLOSE SYMMETRIC KEY AdventureWorks_SymKey;
Either way you do it you notice that you will have to convert the decrypted value returned into a datatype that you can use, as both the DecryptByKeyAutoAsymKey and DecryptByKey will return a VARBINARY datatype. The DecryptByKeyAutoAsymKey function is very useful in views as it doesn't require the OPEN/CLOSE statements.

It is also worth mentioning the GRANT statements that will be required to give the appropriate rights to be able to see this decrypted data for users.
Notice that CONTROL has to be granted to the Asymmetric Key and only VIEW DEFINITION is required on the Symmetric Key. These are the lowest grants necessary for the keys to work for any user needing to execute either of the SELECT commands shown above. The decryption functions will not work unless the appropriate grants are issued on BOTH the Asymmetric and Symmetric Keys.

Now that we have encrypted data in the database and verified that the encryption works by decrypting that same data via a SELECT statement I will use the 4th and final part of this series to show you how to restore backups of a database that contains encrypted data on another server and still be able to decrypt the values.

Friday, January 3, 2014

Encryption in SQL Server (Part 2)

This is part 2 in my posts on setting up encryption in SQL Server. To get the details on how to setup encryption on your SQL Server instance and the database, please read part 1 first.

Now that we have the SQL Server instance and database ready to encrypt our data we can go into details on how the individual columns of data will be encrypted in the database. At this point you also have some decisions on what types of keys you want to have created and how strong the cryptography algorithm will be protecting them.

Before diving into the next layers of keys, it is important to understand the differences between Asymmetric and Symmetric Keys and how SQL Server handles each. This article on TechNet - Cryptography in SQL Server does a great job explaining the differences.

The recommended best practice is to setup an Asymmetric Key as the next layer and make this key as strong as you can and use a Symmetric Key to do the actual data encryption. The reason for this is that Asymmetric Keys require a lot more processing power to use, so you only use the Asymmetric Key to protect the Symmetric Key. The Symmetric Key is what is used to actually encrypt your data, then only when the Symmetric Key is first being accessed does the system have to decrypt the Asymmetric Key. Otherwise you will have to pay the Asymmetric Key processing cost every time a value is being encrypted or decrypted.

In SQL Server you have many options when creating these Asymmetric Keys, but the one most often used is RSA_2048. This is currently the highest level of security that SQL Server supports for Asymmetric Keys. Below is the command you use to create the Asymmetric Key:
(SELECT * FROM sys.asymmetric_keys WHERE [name] = 'AdventureWorks_AsymKey')
The naming of these keys is important, as you will need to reference this key in other commands to do the encryption/decryption so make sure you pick a name you can keep track of.

As mentioned above you will also need a Symmetric Key to do the actual data encryption that uses the Asymmetric Key. With Symmetric Keys you have to use a different set of algorithms because Symmetric Keys work differently than Asymmetric Keys as they have a pair of keys created (public/private). To get a great overview of what Symmetric Key encryption is see this HowStuffWorks article. Below is the command to create a Symmetric Key:
    (SELECT * FROM sys.symmetric_keys WHERE [name] = 'AdventureWorks_SymKey')
    CREATE SYMMETRIC KEY AdventureWorks_SymKey
        WITH KEY_SOURCE = 'I82xskoiw820KOW>282kxow',
            ALGORITHM = AES_256,
            IDENTITY_VALUE = 'w,xi292XWE82S92iqoxl*&23'
        ENCRYPTION BY ASYMMETRIC KEY AdventureWorks_AsymKey; 
I used AES_256 as the algorithm in this Symmetric Key as it is the strongest algorithm allowed at this time in SQL Server. The DES and RC algorithms are not recommended as they have all been cracked and could make your encrypted data accessible.

As I learned how to use these keys in my prototyping I ran into an important issue that I want to highlight here. When you create the Symmetric Key it is very important that you be able to re-create the exact same key if you accidentally lose that key. Since the Symmetric Key is going to be used to do all of the data encryption, if that key is lost you will not be able to get your data back in decrypted form if you lose it. This is where the KEY_SOURCE and IDENTITY_VALUE parameters of that command come into use. As long as you make sure to use those same values then you can recreate the same Symmetric Key (assuming you have the Database Master Key and Asymmetric Key setup the same as well, I will cover how to get this setup if you have moved to another server in a later part of this series).

There are no commands to backup the Asymmetric or Symmetric Keys like we had for the Service Master Key and Database Master Keys. If you decide to use Certificates over the keys, then you can backup the certificates. I have not used certificates in the systems that I setup encryption on, mainly because SQL Server currently does not fully handle certificates as it should. If you would like to get more details on creating certificates in SQL Server, see this TechNet article:

Now we have laid all of the groundwork for setting up encryption on your SQL Server database, so the next part we will start to actually encrypt data and decrypt that data as well!