Sql-server – Cannot Add “Active Directory – Password” as Linked Server into the SSMS

sql serverssms

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)

Best Answer

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
Related Question