SQL 2019 - Data Virtualization - Unable to Create External Data Source from my Laptop (On-Premise) to my Azure SQL Database

Question

Hello,

The title for my question says it all. I am unsuccessful at creating a connection from on-premise to my Azure SQL database. I can successfully connect through SSMS, and Azure Data Studio. Using T-SQL is a no go. I have added my client IP address to the Azure SQL Server firewall. So all is good there. I keep the following message:

OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message "Cannot generate SSPI context".

Msg -2146893042, Level 16, State 1, Line 18
SQL Server Network Interfaces: No credentials are available in the security package
Any help would be appreciated. I thought about checking the error through Kerberos but I feel I am headed in the wrong direction. It's got to be something simple that I am either overlooking or forgot about. I also understand Azure networking and know the difference between a proxy account .vs. a redirect .vs. sungi express route. i have tried adding a virtual network and a private network connection. But once again I think the answer is on simple side and I can't seem to figure it out.

Has anybody else come across this issue.

Thanks

Answer 1

I solved my own problem. First I needed to step away and think clearly. The answer is very simple. In order to solve this you will need to create the same SQL login in both Azure and Premise. From SSMS login with this account using the appropriate permissions assigned. You should be able to do this for Azure and Premise environment (if done correctly!). Once done you will be able to create your external data source. This has nothing to do with Linked Servers!

Answer 2

Please try with this Query

EXEC master.dbo.sp_addlinkedserver 
     @server = N'MyAzureSQLDatabase'
    ,@srvproduct = N''
    ,@provider = N'SQLNCLI'
    ,@datasrc = N'SERVERNAME.database.windows.net'
    ,@catalog = N'DATABASENAME'


EXEC master.dbo.sp_addlinkedsrvlogin 
     @rmtsrvname = N'MyAzureSQLDatabase'
    ,@useself = N'False'
    ,@locallogin = NULL
    ,@rmtuser = N'sqladmin' /* SQL UserName */
    ,@rmtpassword = '***************'   /* Password */
GO

--
If an Answer is helpful, please “Accept Answer” or Up-Vote for the same which might be beneficial to other community members reading this thread.