0

I'm trying to add Azure Database with Active Directory - Password as Linked Server, but its giving error while login.

I'm using Domain account (for e.g. abc@my-domain.com and password)

These credentials are working and I'm able to connect normally BUT NOT WORKING IN LINKED SERVER

EDIT: Adding the screenshots. enter image description here

enter image description here

enter image description here

ERROR DETAILS BELOW:

===================================

Create failed for LinkedServer ''. (SqlManagerUI)

------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17213.0+((SSMS_Rel).171128-2020)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+LinkedServer&LinkId=20476

------------------------------ Program Location:

at Microsoft.SqlServer.Management.SqlManagerUI.LinkedServerPropertiesData.ApplyChanges() at Microsoft.SqlServer.Management.SqlManagerUI.LinkedServerProperties.DoPreProcessExecution(RunType runType, ExecutionMode& executionResult) at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.DoPreProcessExecutionAndRunViews(RunType runType) at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.ExecuteForSql(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult) at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.Microsoft.SqlServer.Management.SqlMgmt.IExecutionAwareSqlControlCollection.PreProcessExecution(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult) at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.RunNow(RunType runType, Object sender)

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------ Program Location:

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry) at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType, Boolean retry) at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries, Boolean retry) at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ExecuteNonQuery(StringCollection queries, Boolean includeDbContext, Boolean executeForAlter) at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImplFinish(StringCollection createQuery, ScriptingPreferences sp) at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()

===================================

Name cannot be NULL. (.Net SqlClient Data Provider)

------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.1000&EvtSrc=MSSQLServer&EvtID=15004&LinkId=20476

------------------------------ Server Name: . Error Number: 15004 Severity: 16 State: 1 Procedure: sys.sp_validname Line Number: 17

------------------------------ Program Location:

at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException) at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)

1
  • 3
    "Not working" could cover many, many circumstance. Please edit the question, adding the precise wording of the error message, and whatever command was issued to produce it. – Michael Green Feb 18 at 12:52
1

First, you need to use the new Microsoft OLEDB Driver for SQL Server, aka "MSOLEDBSQL", not the old Microsoft OLEDB Provider for SQL Server, aka "SQLOLEDB".

The old driver doens't support Azure Active Directory Authentication.

Second you have to set the "Authentication" connection string keyword:

enter image description here

Then when you map the login using AAD credentials, the driver will interpret them correctly, and not try to use SQL Auth.

enter image description here

Or in script form:

USE [master]
EXEC master.dbo.sp_dropserver @server=N'AZURE', @droplogins='droplogins'
GO
EXEC master.dbo.sp_addlinkedserver @server = N'AZURE', @srvproduct=N'', @provider=N'MSOLEDBSQL', @datasrc=N'xxxxxxx.database.windows.net', @provstr=N'Authentication=ActiveDirectoryPassword'

GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'collation compatible', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'AZURE', @locallogin = NULL , @useself = N'False', @rmtuser = N'xxxxxxx@xxxxxxx.onmicrosoft.com', @rmtpassword = N'xxxxxxxxx'
GO
exec ('select 1 a') at AZURE
6
  • Followed the steps.But getting the same error in both ways. either from the wizard panel or using queries. >>> Cannot create an instance of OLE DB provider "MSOLEDBSQL" for linked server "AZURE". (.Net SqlClient Data Provider) – CodeRunner Feb 18 at 22:14
  • Did you install it? The download page is: docs.microsoft.com/en-us/sql/connect/oledb/… – David Browne - Microsoft Feb 18 at 22:27
  • Yes I did. After installation the new option Microsoft OLE DB Driver for SQL Server came into the drop down. – CodeRunner Feb 18 at 22:49
  • And you installed the x64 version of MSOLEDBSQL? Did you try rebooting after the install? – David Browne - Microsoft Feb 18 at 22:52
  • Yes. 64bit. and restarted the SSMS only. Did not reboot the PC yet. – CodeRunner Feb 18 at 22:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.