I recently wrote a post about using Transparent Data Encryption (TDE) with Azure Key Vault as an alternative to managing certificates. Today’s post will explore using SQL Server’s Always Encrypted functionality with Azure Key Vault. Always Encrypted, as with TDE, can use Windows certificates or what an external storage location such as a Hardware Security Module (HSM) or Azure Key...
Transparent Data Encryption with Azure Key Vault
Microsoft introduced Transparent Data Encryption (TDE) to SQL Server with the 2008 release providing a simple, easy way to add an extra layer of protection to our data. TDE is not unique to SQL Server and hasn’t changed much since being introduced into the product. With Azure becoming more and more popular I get requests from customers fairly frequently asking to talk about storing the...
Upcoming Presentation: End to End Security with SQL Server 2016
Each week Pragmatic Works hosts a free training session. The topics include SQL Server internals, Business Intelligence, cloud services and even big data. On Tuesday May 2nd, 2017 I will be presenting the free training session on SQL Server 2016 security. Check out the details below. Hope to see you there. Topic: End-to-End Security with SQL Server 2016 Abstract: It only takes one data breach to...
Importing a BACPAC to SQL Server
We previously looked at Archiving Azure SQL Database to a BACPAC and in today’s post we are going to address how to look at that data by restoring or importing it to a local SQL Server. As discussed in the previous post this discussion arose in a conversation with a customer who needed to keep monthly archives of their data for a 10 year time period to comply with industry regulations and...
Archiving Azure SQL Database to a BACPAC
UPDATE: The other half of this scenario has been posted. Check out Importing a BACPAC to SQL Server. Azure SQL Database is a managed database platform as a service (Paas) offering available from Microsoft in the Azure cloud. One of the advantages to Azure SQL Database is all the file management, server maintenance and backups are taken care of automatically. Point in time recovery is built...
Find SQL Database Owner with T-SQL
One thing that on occasion bugs me about PDW or APS or whatever it happens to be called this week is the lack of a UI to do things. I’m not one of those people who has to have a UI to perform operations. I have no problem creating databases, doing backups or creating tables through code. That being said there are times when a UI is helpful to just look up information. Just today I needed to...
SQl Server 2014 CTP1
CTP 1 of SQL Server 2014 was released this evening. If you have an MSDN or TechNet account you can go grab it now! If you are curious about some of the features here is the Microsoft page and there are plenty of blogs out there as well: This is preview software so be sure to read up on the release notes and install on a virtual machine to ensure nothing mission critical gets messed up. Happy data...
SSIS 2012 Deadlock When Running SSIS from SQL Agent Job
I recently had an interesting situation where an SSIS package was scheduled to run at 1 AM but failed with a deadlock message. This seems a bit strange given that it was a deadlock on the step executing the SSIS package, not an error message from within the package. There was no history of a failure in the SSIS execution logs, just the job failure. As you can see, the error only happened a couple...
The INSTALLSHAREDWOWDIR command line value is not valid.
I was recently making some changes to my laptop setup and installing SQL Server 2008R2 and SQL Server 2012 directly on the computer rather than on my VM. In the case of my setup I had already installed the client tools for 2008R2 and 2012 on my laptop. So in this instance I was simply adding the rest of the SQL tools (IS, RS, AS, BIDS, etc). Much to my surprise when I went to install 2008R2 onto...
Exporting Data Using BCP
BCP, or bulk copy program, has been around in SQL Server for a long time. It is a great way to export large quantities of data very quickly from SQL Server. It can be used to export entire tables or even a custom query. In this post we will focus on doing some simple commands to export data. This is by no means a complete and comprehensive look at BCP. My only intention with this post is to get...