Linked Server to Windows Azure SQL Database

Question

The following script fails on Windows 7, SQL Server 2012 to create a working linked server. I am able to connect from Object Explorer to AdventureWorks2012 on the Windows Azure SQL Database server.

EXEC master.dbo.sp_addlinkedserver @server = N'GAMMA_AZURE',@srvproduct=N'Any', 
     @provider=N'MSDASQL', @datasrc=N'GAMMA_AZURE',
	 @provstr='Server=tcp:sssssss.database.windows.net,1433;Database=AdventureWorks2012;User ID=llllllll;Password=pppppppp;Connection Timeout=100'
GO

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'GAMMA_AZURE',
     @useself=N'False',@locallogin=NULL,
	 @rmtuser=N'llllllll',@rmtpassword='pppppppp'
 
GO

SELECT * FROM OPENQUERY(GAMMA_AZURE, 'SELECT Name, ListPrice FROM Production.Product WHERE Color = ''Blue'' ORDER BY Name;')
/*
OLE DB provider "MSDASQL" for linked server "GAMMA_AZURE" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "GAMMA_AZURE".
*/

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 1

Hello Kalman,

I don't think that the old MSDASQL supports SQL Azure, you should use a current version of "SQL Server Native Client" (SQLNCLI11 for Version 11 = 2012) provider instead. And you should set the "Encrpt=True" property to encrypt the connection:

EXEC master.dbo.sp_addlinkedserver 
    @server = N'TEST', @srvproduct=N'SQLSERVER', 
    @provider=N'SQLNCLI11', 
    @provstr=N'Server=tcp:sssssss.database.windows.net,1433;Database=AdventureWorks2012;User ID=llllllll@servername;Password=pppppppp;Connection Timeout=100;Encrypt=True;'

---

Olaf Helper

Blog Xing

Answer 2

Hi Olaf,

One step ahead. But still error.

This session has been assigned a tracing ID of 
'68df2e8d-4fd7-45ca-8ab6-xxxxxxx'.  Provide this tracing ID to 
customer support when you need assistance.
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'llllllllllllllll'.

What is puzzling, that it is fairly easy to connect to Windows Azure SQL Database server from SSMS 2012. Also quite easy to open an SSIS connection to it.

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 3

One step ahead again! I setup a system DSN and used in the linked server definition.

I can SELECT from some tables like Person.AddressType.

Error with a tiny table.

SELECT * FROM OPENQUERY([GAMMA_AZURE], 'SELECT * FROM HumanResources.Shift');
/*
Msg 7347, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' for linked server 'GAMMA_AZURE' returned data that does not match expected data 
length for column '[MSDASQL].StartTime'. The (maximum) expected data length is 16, while the returned data length is 12.
*/

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 4

Here is the resolution, a bug reported on Connect.

http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/487841e4-0107-4209-b6d9-e9af12af3f58

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012