Share via

On-Prem SQL Linked Server to Azure Active Directory - Universal with MFA SQL

Russ Rosenlof 21 Reputation points
2020-10-01T12:18:38.05+00:00

Hi,

I cannot determine if a linked server connection to an Azure SQL with "Universal with MFA" authentication is supported.

My link FROM server is an on-prem SQL Enteprise Server (13.0.1601.5)

My link TO server is an Azure SQL Server that requires "Universal with MFA" authentication.

I can successfully login to the Azure SQL Server using MFA and SSMS, but I am unable to add it as a linked server.

  1. New Linked Server
  2. Linked Server Name/Adress is entered as <server>.database.windows.net
  3. Server Type: "SQL Server"
  4. Security: "Be Made Using this Security Context"
  5. Enter username and password.
  6. Get "Login Failed for user" error 18456

If I enter the username as "username@keyman .com" instead of just username, I get a different error:
"Cannot open server "domain.com" requested by login. Login failed.

I've also tried some things with connection strings and other SQL Providers, but nothing I do seems to work

Any recommendations?

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
0 comments
Accepted answer
  1. CathyJi-MSFT 21,131 Reputation points Microsoft Vendor
    2020-10-02T08:33:40.863+00:00

    Hi anonymous user,

    Quote the answer from this thread.
    As of now, we cannot create a linked server to Azure SQL DB with MFA authentication type.

    In a domain environment where users are connecting by using their domain logins, selecting Be made using the login's current security context is often the best choice. When users connect to the original SQL Server by using a SQL Server login, the best choice is often to select By using this security context, and then providing the necessary credentials to authenticate at the linked server. Please refer to MS document Create Linked Servers.

    Suggest you using other authentication, you can follow the steps from this blog How to create a linked server to an Azure SQL database .

    Best regards,
    Cathy

    If the response is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 107.2K Reputation points
    2020-10-01T21:56:05.413+00:00

    To start with, you have SQL 2016 RTM. You should apply SP2 and the latest Cumulative Update as soon as you have a maintenance window.

    This will not resolve this problem, but I always point when people are using outdated software.

    I think this is a dead end for you. You need a way to specify that you are using an AAD login, and sp_addlinkedsrvlogin does not take such a parameter. What possibly could work is to add a connection-string argument to the @datasrc argument to sp_addlinkedserver. However, that would require the new MSOLEDBSQL provider, which does not run well with SQL 2016. (And I have not tested that this is actually possible.)

    Without such a parameter, the login attempt will be as SQL authentication, which is not what you want.

    1 person found this answer helpful.