Wednesday, December 30, 2015

Using a Linked Server with Azure

I just spent the better part of an afternoon trying to figure out how to get a linked server to work between a SQL Server 2014 Database and an SQL Azure Database. After a lot of googling and experiments I was finally successful.

Here is what I learned:

  1. Don't use the GUI on the Linked Database node SSMS-- it will lead you places you don't need to go.
  2. The only Microsoft doc I could find with working example code is at, at the very end.
  3. Make sure you are connected to the master database when you run the sp_addlinkedserver, sp_addlinkedsrvlogin, and sp_dropserver stored procedures.
  4. When you run sp_addlinkedserver:
    1. Use 'sqlncli' as the provider.
    2. Use both the server parameter (What you are calling it on the client) AND the datasrc parameter (the actual name of the source database).
    3. srvproduct must be an empty string ('').
  5. You can't use windows logins (SQL Authentication only)

Here is some example code which I hope leads you in the right direction.


Sunday, December 20, 2015

Command Line SQL Server Interpreter

I've turned this into a project on GitHub, See this post for more info

I frequently have to debug data issues and reports on a SQL server database. In a lot of cases I just need to look up a few fields from a record or do a quick foreign key lookup. A SQL query tool or SSMS is overkill for the job because it takes to long to load.  SQLCMD requires a SSMS installation and I don't always have it installed where I'm working. So I wrote this lightweight powershell. script for these situations

Friday, December 11, 2015

Global Temp Tables are a Security Risk

I was working on a project to use bcp and xp_cmdshell to export data. Along the way, stumbled on a great idea to create reusable code and a stored procedure to export the results of any query to a delimited file -- I'd format the data into a global temp table  and export it with  bcp.  I even started working on a stored procedure that would accept parameters for the temp table name, delimiter, and export file name. Great. It's good, and it's fast, its reusable, and, I realized as I was working on it, it's a huge security problem.

The problem is the global temp table.  Here's what Microsoft says about global temp tables in SQL 2008 (the version I'm currently using) :

Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server. [Link]

So what this means is that once I populate my temp table anybody can look at the data.  Not good. I realize that if it's not sensitive data, it's not a big deal. If I use it wisely there shouldn't be a problem. But, once I put it in a stored procedure someone else may want to use it, and they may not realize that this is insecure. I bet this is how security vulnerabilities happen.

Before you flame me, I realize that there are security concerns about the use of xp_cmdshell but those can be mitigated by proper control of the sysadmin role.

More on BCP, if you are interested.

Read this entire thread if you are interested in learning more about xp_cmdshell security issues.