locked
Linked Server to Windows Azure SQL Database RRS feed

  • 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

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


    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
    • Edited by Iric Wen Thursday, February 28, 2013 7:01 AM mark
    • Marked as answer by Iric Wen Thursday, February 28, 2013 7:02 AM
    Wednesday, February 27, 2013 10:41 PM