Post reply

SQL 2016 Linked Server to Azure DB Not Working

  • November 4, 2021 at 3:10 pm

    #3947930

    I am trying to create a linked server to and Azure DB from SQL 2016, SP3. I have used instructions found on SQLShack and SQLTips and successfully did the initial creation. I used a SQL login/user on the Azure DB. However, when I try to connect to the linked server I get an authentication error, 18456. I can connect to the Azure DB using a query window using the same account info, though.

    I created the linked server by script, so I was able to created it, but if I try to modify it, it fail the connection test upon save.

    I don't see any useful debugging info in the error log on the Azure DB (sys.event_log) and the internet has not yielded any useful information.

    Does anyone have any ideas as to why I cannot connect to the Azure linked server?

    Thanks,

    Chris

    • This topic was modified 3 years ago by  Stamey.

    Learning something new on every visit to SSC. Hoping to pass it on to someone else.

  • November 4, 2021 at 9:06 pm

    #3948066

    Since you get an authentication error, I would be willing to bet the problem is with the authentication somewhere.

    Did a quick google, and found this:

    https://www.liquidweb.com/kb/troubleshooting-microsoft-sql-server-error-18456-login-failed-user/

    Not specific to Azure, but should point you in the right direction.  Basically, I'd start with looking at the SQL log to see the state.  There is a table at that above link to say what each state means.  For example, 2 or 5 means that it is a username problem, 6 means a Windows username was used with SQL authentication, etc.

    Narrow down the cause of the problem, and then it should be easier to daignose.

    As a random thought - since you scripted it out, are you using NVARCHAR for the password or VARCHAR?  I am pretty sure that the password SHOULD be NVARCHAR.  Also, if you use ' in your password, you would need to escape that by putting '' (2 ' characters).

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • November 5, 2021 at 1:56 pm

    #3948338

    Thanks for the reply. Yes, it does appear to be an authentication problem, but since I have successfully connected using the necessary credentials, I have ruled out that as a problem.

    My script does pass the strings as NVarChar, as I am passing them into the procedure EXEC master.dbo.sp_addlinkedsrvlogin in the script. The script was generated by SSMS, so I figure it's exactly what SQL wants. No characters that could cause a problem in the password. Also, I have manually opened the linked server properties in SSMS and re-entered the password to make sure it is correct in there.

    I have MS involved now, as I cannot connect to the Azure DB with a Logic App either, using the same creds, so I am wondering if it is the same problem. MS will work with me on the Logic App side and I will see if the solution there applies to the linked server also.

    Thanks,

    Chris

    Learning something new on every visit to SSC. Hoping to pass it on to someone else.

  • November 5, 2021 at 4:00 pm

    #3948398

    Some good advice. A few things I see happen at times, which sometimes feel like authentication, but aren't.

     

    Firewall- if your local IP changes (or your network), sometimes you get a network error here.

    Unavailable. I think with regular systems, and definitely with serverless, sometimes it takes  minute to spin up a path to the db. I often connect with SSMS/ADS first, then try something like an app or linked server.

     

  • November 10, 2021 at 2:38 pm

    #3949938

    I can successfully connect to the Azure DB using a query window in SSMS with the same account I am trying to use for the linked server, and this is on the SQL Server console where the linked server is, so I am sure that I have connectivity through the FW, unless different ports are being used for linked servers, and I'm pretty sure that is not the case.

     

    Thanks,

    Chris

    Learning something new on every visit to SSC. Hoping to pass it on to someone else.

  • August 2, 2022 at 10:25 am

    #4071277

    Stamey wrote:

    I am trying to create a linked server to and Azure DB from SQL 2016, SP3. I have used instructions found on SQLShack and SQLTips and successfully did the initial creation. I used a SQL login/user on the Azure DB. However, when I try to connect to the linked server I get an authentication error, 18456. I can connect to the Azure DB using a query window using the same account info, though.

    I created the linked server by script, so I was able to created it, but if I try to modify it, it fail the connection test upon save.

    I don't see any useful debugging info in the error log on the Azure DB (sys.event_log) and the internet has not yielded any useful information.

    Does anyone have any ideas as to why I cannot connect to the Azure linked server? Omegle.site

    Thanks, Chris

    The issue got solved.

    • This reply was modified 2 years, 2 months ago by  adamgill.
    • This reply was modified 1 year, 10 months ago by  adamgill.

  • August 22, 2022 at 9:06 am

    #4079184

    And the implemented solution is / was?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply