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):
RESTORE SERVICE MASTER KEY
    FROM FILE = 'C:\localhost_SMK.smk'
    DECRYPTION BY PASSWORD = 'ADa329wopkj*&ER.slkqksl';
http://technet.microsoft.com/en-us/library/ms187972(v=sql.105).aspx
 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' 
http://technet.microsoft.com/en-us/library/ms186336(v=sql.105).aspx
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:
OPEN MASTER KEY
    DECRYPTION BY PASSWORD = 'Ils20*(LKjqwnslqo372,cklweLKHJn';
ALTER MASTER KEY
    ADD ENCRYPTION BY SERVICE MASTER KEY;
CLOSE MASTER KEY;
http://technet.microsoft.com/en-us/library/ms186937(v=sql.105).aspx
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):
IF NOT EXISTS
 (SELECT * FROM sys.asymmetric_keys WHERE [name] = 'AdventureWorks_AsymKey')
 CREATE ASYMMETRIC KEY AdventureWorks_AsymKey WITH ALGORITHM = RSA_2048;
http://technet.microsoft.com/en-us/library/ms188357(v=sql.105).aspx 
IF NOT EXISTS
    (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;
http://technet.microsoft.com/en-us/library/ms174430(v=sql.105).aspx 
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;
http://technet.microsoft.com/en-us/library/ms365420(v=sql.105).aspx
OPEN SYMMETRIC KEY AdventureWorks_SymKey DECRYPTION BY ASYMMETRIC KEY AdventureWorks_AsymKey; 
SELECT Phone_encrypt, Phone, CONVERT(nvarchar(25),DecryptByKey(Phone_encrypt))) AS 'CustomerPhone_decrypted' FROM Customer.Contact; 
CLOSE SYMMETRIC KEY AdventureWorks_SymKey; 
http://technet.microsoft.com/en-us/library/ms181860(v=sql.105).aspx
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!