locked
Linked Server login error RRS feed

  • Question

  • Hi Community,

    I have problems accessing my azure-sql-server from onPrem.

    -          I’v created a private endpoint for my sql-server, to access it from onPrem trough the VPN

    -          I’ve opened Port 1433 to my onPrem SQL-Server in the firewall ruleset to the azure private endpoint

    -          A host entry for the azure sql-server pointing to the IP of the private endpoint, on my onPrem SQL.

    -          I’ve created a login an  mapped user in the azure SQL server

    Now I’m able to access the azure-SQL-server over the SSMS from onPrem.

    But if I try to create a linked server entry for the azure SQL Server I get a login failed.

    I’ve tried to create the linked server as server Type SQL Server and as SQL OLE Connection, but I always gets an login failure error.

    OnPrem SQL is version 2017 (14.0.3281.6)

    I don’t know what else I could try to connect the serves.

    Thanks and regards

    Guido

    Sunday, March 22, 2020 12:33 PM

Answers

  • So, with help from the azure support, we found the solution:

    1. You always have to create a linked server object as “Microsoft OLE Provider”
    2. you must specify the Catalog, so the connection is dedicated to one database.
    3. The login to the SQL-Server must be in the form Account@sqlservername, not account@fqdn.
    4. If a private endpoint is used, only the firewallport 1433 (SQL) must be opend


    • Marked as answer by Guido Jeuken Wednesday, April 15, 2020 7:33 PM
    Wednesday, April 15, 2020 7:33 PM

All replies

  • Hi Guido,

    The following is the documentation on how to set this up but wanted to inquire about VPN. Are you using Point-to-Site VPN or Site-to-Site VPN? If you are using Point-to-Site VPN, your connection from your workstation/Laptop is using VPN but the on-premise SQL Server connection to your provisioned Azure SQL Database is not routed through VPN. If you have implemented Site-to-Site, then can you detail the specific login failure/error message? What is the security context you are using?

    Create Linked Servers (SQL Server Database Engine)

    Local login
    Specify the local login that can connect to the linked server. The local login can be either a login using SQL Server Authentication or a Windows Authentication login. Use this list to restrict the connection to specific logins, or to allow some logins to connect as a different login.

    Impersonate
    Pass the username and password from the local login to the linked server. For SQL Server Authentication, a login with the exact same name and password must exist on the remote server. For Windows logins, the login must be a valid login on the linked server.

    To use impersonation, the configuration must meet the requirement for delegation.

    Remote User
    Use the remote user to map users not defined in Local login. The Remote User must be a SQL Server Authentication login on the remote server.

    I hope this information is helpful. If you have additional questions or are still having issues, please detail the issue. If you resolve this, please detail the resolution.

    Regards,

    Mike

    Tuesday, March 24, 2020 12:13 AM
  • Hi, Mike,

    Thank you for your answer.
    It's quite funny, the screenshots are made on the same onPrem SQL-Server.
    With a normal SQL connection I can access the databases, but not through the linked server object. I use SQL-Server authentication, username and password are identical in both connection types.
    The VPN connection is a side-to-side connection.
    The private endpoint has an IP from the Vnet of the VWAN and is accessible. I have solved the name resolution on the SQL-Server name via a local host file, IP of the private endpoint -> SQL-Server name.

    Thanks and regards

    Guido

    

    Tuesday, March 24, 2020 10:09 AM
  • Hi all,

    I've opened a MS-Call for this problem. 

    I'll update the post, if we've found the solution.

    Regards,

    Guido

    Tuesday, March 24, 2020 5:46 PM
  • Please do. I am not sure if you have the ability to reference the specific database or if the value is set to default. All your configuration looks correct. 

    Thanks,

    Mike

    Tuesday, March 24, 2020 9:56 PM
  • Just checking in to see if your issue was resolved by Azure Support

    It would be helpful to detail the solution here as it will be helpful to others attempting to address the same issue.

    Friday, March 27, 2020 1:55 PM
  • So, with help from the azure support, we found the solution:

    1. You always have to create a linked server object as “Microsoft OLE Provider”
    2. you must specify the Catalog, so the connection is dedicated to one database.
    3. The login to the SQL-Server must be in the form Account@sqlservername, not account@fqdn.
    4. If a private endpoint is used, only the firewallport 1433 (SQL) must be opend


    • Marked as answer by Guido Jeuken Wednesday, April 15, 2020 7:33 PM
    Wednesday, April 15, 2020 7:33 PM
  • Thank you for the update. This detail is super helpful. 

    Regards,

    Mike

    Wednesday, April 15, 2020 7:39 PM