Answered by:
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
- Edited by Kalman Toth Saturday, February 23, 2013 5:59 PM
Saturday, February 23, 2013 5:53 PM
Answers
-
Here is the resolution, a bug reported on Connect.
Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012Wednesday, February 27, 2013 10:41 PM
All replies
-
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- Edited by Olaf HelperMVP Sunday, February 24, 2013 11:53 AM
Sunday, February 24, 2013 11:53 AM -
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
- Edited by Kalman Toth Sunday, February 24, 2013 6:53 PM
Sunday, February 24, 2013 3:44 PM -
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
- Edited by Kalman Toth Wednesday, February 27, 2013 11:12 PM
Wednesday, February 27, 2013 6:04 PM -
Here is the resolution, a bug reported on Connect.
Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012Wednesday, February 27, 2013 10:41 PM