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 18, 2014
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!
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)
SQLSaturday #285 - Atlanta, GA (5/3/14)
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.
Master.zip
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
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:
The command to populate the new encrypted value column is a simple UPDATE command that you are already used to:
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:
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.
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.
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.ContactThe 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.
ADD Phone_encrypt VARBINARY(128);
The command to populate the new encrypted value column is a simple UPDATE command that you are already used to:
OPEN SYMMETRIC KEY AdventureWorks_SymKey DECRYPTION BY ASYMMETRIC KEY AdventureWorks_AsymKey;
UPDATE Person.Contact SET Phone_encrypt = EncryptByKey(Key_GUID('AdventureWorks_SymKey'), Phone);
CLOSE SYMMETRIC KEY AdventureWorks_SymKey;
http://technet.microsoft.com/en-us/library/ms190499(v=sql.105).aspxhttp://technet.microsoft.com/en-us/library/ms174361(v=sql.105).aspxhttp://technet.microsoft.com/en-us/library/ms177938(v=sql.105).aspxThis 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;
http://technet.microsoft.com/en-us/library/ms365420(v=sql.105).aspxAnd the other way is to open the Symmetric Key as was done above to perform the UPDATE and use it to do the decryption:
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).aspxEither 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.
GRANT CONTROL ON ASYMMETRIC KEY::AdventureWorks_AsymKey TO User1;
GRANT VIEW DEFINITION ON SYMMETRIC KEY::AdventureWorks_SymKey TO User1;
http://technet.microsoft.com/en-us/library/ms187991(v=sql.105).aspxhttp://technet.microsoft.com/en-us/library/ms179887(v=sql.105).aspxNotice 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:
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:
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: http://technet.microsoft.com/en-us/library/ms187798(v=sql.105).aspx.
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!
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:
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).aspxThe 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:
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).aspxI 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: http://technet.microsoft.com/en-us/library/ms187798(v=sql.105).aspx.
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!
Tuesday, December 31, 2013
Best of 2013
As 2013 comes to a close I wanted to highlight some of what I think are the best of 2013 that I enjoyed outside of work.
Best Movies
2013 was a great year for movies and by far, Gravity was the best of the year. I was able to see Gravity in a large format digital theater with 3D and Dolby Atmos, which made for an amazing experience that I felt like I was floating right alongside Sandra and George in space! FF6 extended that franchise even more and has me looking forward to FF7 in 2015. Not sure what the death of Paul Walker with do to the series future, but I am happy to see that they are going to push ahead and finish it and release it in April of 2015. Man of Steel was the best of the superhero movies in 2013, and is the first time a good Superman movie has been done since 1978.
Best TV
TV did have some stand out new shows this year, but the new thing for me this year was discovering 2 shows and binge watching them both online. The first one that I binge watched was Orphan Black and it shot straight up to the top of my list as the best TV show of 2013! They have done a great job in the first season of keeping us going with the whole clone storyline, if you have not seen this show from BBC America yet, then you should. Unfortunately it is not yet available on any streaming services, I ended up buying the first season from Amazon Video and watched all 10 episodes in less than 1 day, that is how hooked I was! The other one that I discovered is The Fall, which a serial killer mini-series starring Gillian Anderson (The X-Files) set in Northern Ireland and with Gillian doing her Brit accent (she lived in Britain early in her life and moved back after The X-Files finished). The Goldbergs is one of the funniest shows that I have seen since Big Bang Theory, and most of that is probably because of the setting in the early-mid 1980's. Growing up in the 80's, this show just brings back so many memories, they have done their homework and you just have to look around at the props on the set to see that. Agents of S.H.I.E.L.D. did not make my top 10, mainly because it just didn't live up to the hype, I have high hopes that it can do better in 2014.
Best Music
I don't listen to a lot of new music, but the one album that I played over and over in 2013 was Random Access Memories from Daft Punk. It has been amazing to watch them change their sound over and over and evolve from just another electronic group to a true musical group. The mix of 70's funk/disco to their music made this by far my favorite album of the year. Most of the rest are just a bunch of individual singles from different artists (JT, Katy Perry, Lorde, Miley Cyrus, etc.), but Random Access Memories was the best overall album. The rest of what I listened to in 2013 were soundtracks from all of the movies listed above and more. I think that my favorite soundtrack of the year would have to be Man of Steel. I never thought that anyone could make a better Superman theme or soundtrack than John Williams, but Hans Zimmer did for Superman what he did for Batman and created some amazing new themes. The themes in Man of Steel were so alien sounding, but yet heroic and strong at the same time, which captures exactly what Superman is.
Best Gadgets
For best tech gadget of 2013, it would be the new Apple iPad Mini with Retina. I was a long time full-size iPad user, but I found that it was too big to travel with and when Apple finally announced the new iPad Mini with Retina display, then I decided it was time to switch. I love how small and light it is and I find myself taking it with me everywhere. I also made sure to get mine with Verizon 4G, which I also had on my previous iPad and would never consider getting a tablet without 4G. It's just far too convenient to use it anywhere and not have to worry about finding wi-fi. I also love my MacBook Air that I got at the very beginning of the year, the only issue now is the iPad Mini has become my go to device, so the Air doesn't get a lot of use. I also made the big switch from iPhone to Windows Phone in 2013, when the Nokia Lumia 1020 came out. I had been thinking about this switch for awhile and when iOS7 didn't make the big change that I was hoping from I knew it was time to switch. The 41MP camera on the Lumia 1020 is amazing and I find myself taking a lot more pictures now. The Windows Phone 8 OS took some getting used to, but now I can do everything that I need to and paired with my iPad Mini, I get the best of both worlds. A couple of other great gadgets that I use with both the iPad Mini and the Lumia 1020 are the headsets, Bose QuietComfort 15 and Bluez AfterShokz. I use the Bose on the plane or anytime I need to use active noise cancelling, while the AfterShokz are great for in airport or office wear when I still want to hear what is going on around me. While I did get the new Xbox One, the verdict is still out on that one. I tried it connected to my Dish Network DVR, but since it can't control everything on the box, just change channels it was very limiting since I do watch most TV from DVR, not live. And the voice commands that they keep boasting are still not there and I find myself yelling at it because it appears to not understand me most of the time. The new Kinect camera is amazing and works much better than the previous generation.
Best Enterprise Tech
And for something more related to my work and this blog, my favorite new enterprise tech that I haven't spent nearly enough time with this year, Microsoft's Power BI! I plan to spend a lot more time learning all of the features of Power BI and hope to post a bunch of blog posts about it, as I learn how to use it.
Thanks to everyone that reads my blog, and I hope you had a great 2013 and are looking forward to an awesome 2014!
Best Movies
- Gravity
- Fast & Furious 6
- Man of Steel
- Pacific Rim
- The Hobbit: Desolation of Smaug
- Iron Man 3
- Thor: Dark World
- Lincoln
- American Hustle
- The World's End
2013 was a great year for movies and by far, Gravity was the best of the year. I was able to see Gravity in a large format digital theater with 3D and Dolby Atmos, which made for an amazing experience that I felt like I was floating right alongside Sandra and George in space! FF6 extended that franchise even more and has me looking forward to FF7 in 2015. Not sure what the death of Paul Walker with do to the series future, but I am happy to see that they are going to push ahead and finish it and release it in April of 2015. Man of Steel was the best of the superhero movies in 2013, and is the first time a good Superman movie has been done since 1978.
Best TV
- Orphan Black
- Doctor Who
- Game of Thrones
- The Walking Dead
- The Goldbergs
- Big Bang Theory
- Almost Human
- Broadchurch
- Masters of Sex
- The Fall
TV did have some stand out new shows this year, but the new thing for me this year was discovering 2 shows and binge watching them both online. The first one that I binge watched was Orphan Black and it shot straight up to the top of my list as the best TV show of 2013! They have done a great job in the first season of keeping us going with the whole clone storyline, if you have not seen this show from BBC America yet, then you should. Unfortunately it is not yet available on any streaming services, I ended up buying the first season from Amazon Video and watched all 10 episodes in less than 1 day, that is how hooked I was! The other one that I discovered is The Fall, which a serial killer mini-series starring Gillian Anderson (The X-Files) set in Northern Ireland and with Gillian doing her Brit accent (she lived in Britain early in her life and moved back after The X-Files finished). The Goldbergs is one of the funniest shows that I have seen since Big Bang Theory, and most of that is probably because of the setting in the early-mid 1980's. Growing up in the 80's, this show just brings back so many memories, they have done their homework and you just have to look around at the props on the set to see that. Agents of S.H.I.E.L.D. did not make my top 10, mainly because it just didn't live up to the hype, I have high hopes that it can do better in 2014.
Best Music
I don't listen to a lot of new music, but the one album that I played over and over in 2013 was Random Access Memories from Daft Punk. It has been amazing to watch them change their sound over and over and evolve from just another electronic group to a true musical group. The mix of 70's funk/disco to their music made this by far my favorite album of the year. Most of the rest are just a bunch of individual singles from different artists (JT, Katy Perry, Lorde, Miley Cyrus, etc.), but Random Access Memories was the best overall album. The rest of what I listened to in 2013 were soundtracks from all of the movies listed above and more. I think that my favorite soundtrack of the year would have to be Man of Steel. I never thought that anyone could make a better Superman theme or soundtrack than John Williams, but Hans Zimmer did for Superman what he did for Batman and created some amazing new themes. The themes in Man of Steel were so alien sounding, but yet heroic and strong at the same time, which captures exactly what Superman is.
Best Gadgets
For best tech gadget of 2013, it would be the new Apple iPad Mini with Retina. I was a long time full-size iPad user, but I found that it was too big to travel with and when Apple finally announced the new iPad Mini with Retina display, then I decided it was time to switch. I love how small and light it is and I find myself taking it with me everywhere. I also made sure to get mine with Verizon 4G, which I also had on my previous iPad and would never consider getting a tablet without 4G. It's just far too convenient to use it anywhere and not have to worry about finding wi-fi. I also love my MacBook Air that I got at the very beginning of the year, the only issue now is the iPad Mini has become my go to device, so the Air doesn't get a lot of use. I also made the big switch from iPhone to Windows Phone in 2013, when the Nokia Lumia 1020 came out. I had been thinking about this switch for awhile and when iOS7 didn't make the big change that I was hoping from I knew it was time to switch. The 41MP camera on the Lumia 1020 is amazing and I find myself taking a lot more pictures now. The Windows Phone 8 OS took some getting used to, but now I can do everything that I need to and paired with my iPad Mini, I get the best of both worlds. A couple of other great gadgets that I use with both the iPad Mini and the Lumia 1020 are the headsets, Bose QuietComfort 15 and Bluez AfterShokz. I use the Bose on the plane or anytime I need to use active noise cancelling, while the AfterShokz are great for in airport or office wear when I still want to hear what is going on around me. While I did get the new Xbox One, the verdict is still out on that one. I tried it connected to my Dish Network DVR, but since it can't control everything on the box, just change channels it was very limiting since I do watch most TV from DVR, not live. And the voice commands that they keep boasting are still not there and I find myself yelling at it because it appears to not understand me most of the time. The new Kinect camera is amazing and works much better than the previous generation.
Best Enterprise Tech
And for something more related to my work and this blog, my favorite new enterprise tech that I haven't spent nearly enough time with this year, Microsoft's Power BI! I plan to spend a lot more time learning all of the features of Power BI and hope to post a bunch of blog posts about it, as I learn how to use it.
Thanks to everyone that reads my blog, and I hope you had a great 2013 and are looking forward to an awesome 2014!
Thursday, December 26, 2013
Encryption in SQL Server (Part 1)
Recently I have been doing some work to add encryption to an existing SQL Server 2008R2 database for a client and I learned a lot about how TDE or Transparent Data Encryption works in SQL Server. The requirements for adding encryption in this case was that only certain columns in some tables of the databases would be encrypted, so I could not encrypt the entire database or tables in a database (this is possible in SQL Server though). Also one of the main requirements was to leave the datatype for the column that would be encrypted the same, and mask the data in that column. So, all of the actual encrypted columns would be new columns added to the appropriate tables. It was also required that the only way to access the decrypted values was by using new views that did the decryption.
With those requirements set I started to learn how to setup encryption in SQL Server and found many TechNet articles that helped me. To begin with some of the great resources for introduction to encryption in general are:
In SQL Server you start with the Service Master Key (SMK) which is setup for your automatically when you install a SQL Server instance. This key is protected by the Windows OS itself using the Data Protection API.
The next layer of key is the Database Master Key (DMK), which is required on each database that you will be using encryption in. The DMK is created by issuing a command on the database you want to create it on.
In order to create the layers of keys required to actually do the encryption of your data you must have the DMK already created in each database. There can be only one DMK per database in SQL Server and it is protected using the password supplied and the Triple DES algorithm (AES_256 is used in SQL Server 2012 and above). Since automatic decryption of the DMK is required for use by other SQL Server commands, a copy of the DMK is also protected using the SMK and stored in the database it is created in along with the master database. This copy of the DMK is the one that can be updated easily as you move the database or it's backups from server to server and still allows you access to the encrypted data without requiring you to decrypt all of the contents and then re-encrypt them again using the new DMK. Updating the DMK is done with the ALTER MASTER KEY command (http://technet.microsoft.com/en-us/library/ms186937(v=sql.105).aspx).
Since all encryption is dependent on both the SMK and DMK, it is also good practice to backup both of these keys to files for safe keeping, which can be done with 2 commands:
The password that is supplied with these BACKUP commands is only used to encrypt the files that are created on the file system. In order to restore these files, the appropriate passwords will be required, so they should be kept safe for future reference (as should all passwords created for these keys).
This will get your SQL Server instance and database all setup and ready to encrypt the actual data stored in them.
In my next post I will cover what Asymmetric Keys and Symmetric Keys are and how they are created and used in SQL Server to encrypt your data.
With those requirements set I started to learn how to setup encryption in SQL Server and found many TechNet articles that helped me. To begin with some of the great resources for introduction to encryption in general are:
- Understanding Transparent Data Encryption - http://technet.microsoft.com/en-us/library/bb934049(v=sql.105).aspx
- Encryption Hierarchy - http://technet.microsoft.com/en-us/library/ms189586(v=sql.105).aspx
- SQL Server and Database Encryption Keys (Database Engine) - http://technet.microsoft.com/en-us/library/bb964742(v=sql.105).aspx
In SQL Server you start with the Service Master Key (SMK) which is setup for your automatically when you install a SQL Server instance. This key is protected by the Windows OS itself using the Data Protection API.
The next layer of key is the Database Master Key (DMK), which is required on each database that you will be using encryption in. The DMK is created by issuing a command on the database you want to create it on.
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'Ils20*(LKjqwnslqo372,cklweLKHJn'
http://technet.microsoft.com/en-us/library/ms174382(v=sql.105).aspx
In order to create the layers of keys required to actually do the encryption of your data you must have the DMK already created in each database. There can be only one DMK per database in SQL Server and it is protected using the password supplied and the Triple DES algorithm (AES_256 is used in SQL Server 2012 and above). Since automatic decryption of the DMK is required for use by other SQL Server commands, a copy of the DMK is also protected using the SMK and stored in the database it is created in along with the master database. This copy of the DMK is the one that can be updated easily as you move the database or it's backups from server to server and still allows you access to the encrypted data without requiring you to decrypt all of the contents and then re-encrypt them again using the new DMK. Updating the DMK is done with the ALTER MASTER KEY command (http://technet.microsoft.com/en-us/library/ms186937(v=sql.105).aspx).
Since all encryption is dependent on both the SMK and DMK, it is also good practice to backup both of these keys to files for safe keeping, which can be done with 2 commands:
BACKUP SERVICE MASTER KEY
TO FILE = 'C:\localhost_SMK.smk'
ENCRYPTION BY PASSWORD = 'ADa329wopkj*&ER.slkqksl'
http://technet.microsoft.com/en-us/library/ms190337(v=sql.105).aspx
BACKUP MASTER KEY
TO FILE = 'C:\localhost_AdventureWorks_DMK.dmk'
ENCRYPTION BY PASSWORD = 'U982LKJOWlkslpq&^@#lskjnkxOPx.w'
http://technet.microsoft.com/en-us/library/ms174387(v=sql.105).aspx
The password that is supplied with these BACKUP commands is only used to encrypt the files that are created on the file system. In order to restore these files, the appropriate passwords will be required, so they should be kept safe for future reference (as should all passwords created for these keys).
This will get your SQL Server instance and database all setup and ready to encrypt the actual data stored in them.
In my next post I will cover what Asymmetric Keys and Symmetric Keys are and how they are created and used in SQL Server to encrypt your data.
Friday, December 13, 2013
SQLSaturday #271 Albuquerque
Now you will be able to see it as a full hour long session and dig into all the details on using a single script to create a Date Dimension that can cover all of the uses you can think of for a data warehouse! And yes, if you can't tell from the title of the session, there will be references to my current favorite TV show, Doctor Who mixed in! :)
Please register to attend SQLSaturday #271 and I'm looking forward to going into Albuquerque early to enjoy the area with my family and see all of you at the event on 1/25!
Wednesday, December 11, 2013
Denver SQL Server User Group Holiday Party
Instead of our normal monthly meeting next Thursday (12/19), the Denver SQL Server User Group will host a Holiday Networking Party sponsored by TekSystems at Great Northern Tavern (8101 East Belleview Avenue, Denver CO 80237). We will have limited space, so if you want to come you will need to RSVP at http://denversqlugholiday2013.eventbrite.com. The party will start at 5:30pm and go until we get kicked out of the room or run of things to talk about! There will be appetizers and a limited number of drink tickets as well as some prizes to give away. It will be a great time to get together and just talk about whatever we want to, no formal presentations, just food, drink and good company! :)As a courtesy to others that may want to come, please only RSVP if you know you can attend and if you have RSVP'd and find that you can no longer attend, please cancel your RSVP via the EventBrite page.
Also, while I'm talking about DSSUG, just wanted to send out a quick thank you to everyone that used our PASS Summit 2013 registration discount code. The group will receive $250 from PASS because of the number of people that used the code for their registration to PASS Summit 2013! We will be sure to put that money to good use for the group in 2014. Keep your eyes out in 2014 for more of this type of registration discount code for other conferences.
Subscribe to:
Posts (Atom)