1

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)

0

1 Answer 1

3

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". You can download it here.

The old driver doesn'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
0

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.