SQL Azure: Adventures in connectivity

SQL Server Management Studio in 2008 R2 (and there's a standalone upgrade for SSMS) supports connecting to SQL Azure. And there were some tools on CodePlex that did too. And that's what I'd always used. But reading along in the latest docs I came across the following statement "Connecting to SQL Azure by using OLE DB is not supported". Later on, the docs talked about using SQLCMD (which is a SQL Server utility that uses OLE DB) and SSIS (which can use ADO.NET/ODBC, but uses OLE DB as API of choice) being supported.

So I decided to try an experiment using SQLCMD (which should be supported), ODBC Data Sources (which should work), and a UDL file (OLE DB, should not be supported). And leave the SSIS question for another day. This turned out to be harder than it sounded. The books online mentioned using (with SQLCMD):

SQLCMD -U {Login@ServerName} -P {password} -S {ServerName} -d master

But the ServerName in question is either a localname (e.g. foo) or DNS name (e.g. tcp:foo.database.windows.net). Turns out that the DNS name must be used for the -S operand (unless you put together a host table) and Login@ServerName MUST specify the localname (no database.windows.net suffix). So this would be:

SQLCMD -U Login@foo -P {password} -S tcp:foo.database.windows.net -d master

And the "tcp:" prefix on server name turned out to be optional if the have your client stack set up for TCP (or TCP & SharedMemory) only.

Got it. This turned out to be key in getting the connection to work in all three cases. But why is OLE DB not supported? It appears to work, the UDL file worked as well as ODBC Driver Manager. Or did it? Turned out that when I tried the dropdown list that enumerates databases in the UDL editor, I received "Connection success, but could not list databases". Trying the database name in worked fine. Hmmm…so here's my wild guess. The SQL Server OLE DB provider will on occasion use its own stored procedures to obtain metadata (like sp_columns_rowset) which retrieve the exact metadata that the OLE DB provider expects to see. Perhaps they didn't bring those along to SQL Azure. Or maybe its just the extra test cycles for an additional API.

But it would be nice to know why OLE DB appears to work but is not supported (except in some utilities). And whether using the OLE DB source and destination in SSIS is recommended.