none
Linked Service Error - Integration runtime

    Question

  • Connection failed

    [{"code":9056,"message":"Cannot connect to SQL Server: '<on-prem server', Database: 'care', User: 'on-prem domain login'."}]

    I don't have a clue what this error code means - any help is appreciated

    Friday, June 22, 2018 2:53 AM

All replies

  • 1. Could you check whether your selfhosted IR is online? If not, please restart it.

    2. Could you provide the activity id in the error message? what authentication type are you using?


    Friday, June 22, 2018 3:20 AM
  • Could you also share the Json of the LinkedService. From error message, the sql server which IR is trying to connect is "<on-prem server" and user name is "on-prem domain login". Looks these 2 values in LinkedService is unexpected.

    BTW, is this error message from Madrid UI TestConnection or Copy Activity result.

    Regards,

    Gary

    Friday, June 22, 2018 4:03 AM
  • The on-prem self hosted ir is running - the connection attempt is from Copy Data Activtiy - the account is a good AD account and logon works on the on-prem server - here is the actual json error message with proprietary data removed -/ 
    Connection failed

    [{"code":9056,"message":"Cannot connect to SQL Server: '<removed server name>', Database: ',removed db name', User: 'removed domain account'."}]

    Any thoughts or ideas much appreciated - btw, this domain account IS NOT in the CORP (azure) AD - I was told it doesn't need to be, but that is the only thing I can come up with

    Friday, June 22, 2018 9:04 PM
  • Could you share your subscriptionId so that I can search from our backend log.

    Besides, is the self-hosted IR installed on the same machine with the Sql Server?

    Regards,

    Gary

    Monday, June 25, 2018 2:23 PM
  • Please don't share your subscription id here.

    Gary, Please share your email address to get the subscription id of the user.

    Monday, June 25, 2018 9:49 PM
    Moderator
  • is your azure sql server enabled for 'Allow access to Azure services'?  I found this solved my problem with the datafactory accessing an azure sql server.  Check the sql server (firewalls and virtual networks), it should be at the top.  If it's OFF, turn it ON.  You can do this via powershell as well, running New-AzureRmSqlServerFirewallRule -ResourceGroupName <ResourceGroupName> -ServerName <SqlServerName> -AllowAllAzureIPs

    Note though, google for azure sql server database firewall configure, there's a URL (I can't seem to add a URL here yet) and the 'important', as it allows ALL connections from Azure, including subscriptions from other customers...  

    Test it out, if it fixes your problem, and if you are worried about security issues, then figure out firewall rules that block/grant.  I can't help you out there, I am about to start down this path myself.

    Friday, August 31, 2018 5:45 PM
  • Thanks, wish this was easier to find.
    Tuesday, September 4, 2018 4:07 PM