{"id":618,"date":"2010-01-06T22:04:00","date_gmt":"2010-01-06T22:04:00","guid":{"rendered":"\/blogs\/bobb\/post\/SQL-Azure-Adventures-in-connectivity.aspx"},"modified":"2010-01-06T22:04:00","modified_gmt":"2010-01-06T22:04:00","slug":"sql-azure-adventures-in-connectivity","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-azure-adventures-in-connectivity\/","title":{"rendered":"SQL Azure: Adventures in connectivity"},"content":{"rendered":"

\nSQL 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.\n<\/p>\n

\nSo 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):\n<\/p>\n

\nSQLCMD -U {Login@ServerName<\/a>} -P {password} -S {ServerName} -d master\n<\/p>\n

\nBut 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<\/a> MUST specify the localname (no database.windows.net suffix). So this would be:\n<\/p>\n

\nSQLCMD -U Login@foo<\/a> -P {password} -S tcp:foo.database.windows.net -d master\n<\/p>\n

\nAnd 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.\n<\/p>\n

\nGot 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.\n<\/p>\n

\nBut 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.<\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[27],"tags":[],"class_list":["post-618","post","type-post","status-publish","format-standard","hentry","category-sql-azure-database"],"yoast_head":"\nSQL Azure: Adventures in connectivity - Bob Beauchemin<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-azure-adventures-in-connectivity\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Azure: Adventures in connectivity - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-azure-adventures-in-connectivity\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2010-01-06T22:04:00+00:00\" \/>\n<meta name=\"author\" content=\"Bob Beauchemin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Bob Beauchemin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-azure-adventures-in-connectivity\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-azure-adventures-in-connectivity\/\",\"name\":\"SQL Azure: Adventures in connectivity - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2010-01-06T22:04:00+00:00\",\"dateModified\":\"2010-01-06T22:04:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-azure-adventures-in-connectivity\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-azure-adventures-in-connectivity\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-azure-adventures-in-connectivity\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Azure SQL Database\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/sql-azure-database\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"SQL Azure: Adventures in connectivity\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\",\"name\":\"Bob Beauchemin\",\"description\":\"SQL Server Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\",\"name\":\"Bob Beauchemin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"caption\":\"Bob Beauchemin\"},\"sameAs\":[\"http:\/www.sqlskills.com\/blogs\/bobb\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Azure: Adventures in connectivity - Bob Beauchemin","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-azure-adventures-in-connectivity\/","og_locale":"en_US","og_type":"article","og_title":"SQL Azure: Adventures in connectivity - Bob Beauchemin","og_description":"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 […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-azure-adventures-in-connectivity\/","og_site_name":"Bob Beauchemin","article_published_time":"2010-01-06T22:04:00+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-azure-adventures-in-connectivity\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-azure-adventures-in-connectivity\/","name":"SQL Azure: Adventures in connectivity - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2010-01-06T22:04:00+00:00","dateModified":"2010-01-06T22:04:00+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-azure-adventures-in-connectivity\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-azure-adventures-in-connectivity\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/sql-azure-adventures-in-connectivity\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"Azure SQL Database","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/sql-azure-database\/"},{"@type":"ListItem","position":3,"name":"SQL Azure: Adventures in connectivity"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/","name":"Bob Beauchemin","description":"SQL Server Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e","name":"Bob Beauchemin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","caption":"Bob Beauchemin"},"sameAs":["http:\/www.sqlskills.com\/blogs\/bobb\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/618","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/comments?post=618"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/618\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=618"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=618"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=618"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}