locked
Adding SQL as Iaas as a data store in data factory RRS feed

  • Question

  • Hi

    I need to add my SQL in Azure VM as a data store.(To use it as "SqlSink")

    I installed the data gateway management software in that VM and registered it. While adding SQL credentials, it is showing error. But I am able to connect to that SQL from my system.

    The "Setting Credentials" dialog says "The SQL server data store 'xxxx' cannot be reached. Check connection settings for" - the sentence is incomplete.

    Can anyone please help?

    Friday, February 20, 2015 9:06 AM

Answers

All replies

  • I haven't tried this scenario yet, but shooting from the hip: The most common error I saw with SQL in Azure VM is the firewall.

    Did you open up the ports on your VM to allow traffic from other Azure services to come in to that SQL Server?

    Is it a default instance (tcp 1433) or a named instance (udp 1434 for browser + dynamic tcp port)?

    These might help:

    http://blogs.msdn.com/b/ggaurav/archive/2014/01/08/connect-to-sql-database-on-azure-iaas-from-ssms.aspx

    http://social.technet.microsoft.com/wiki/contents/articles/3808.security-considerations-for-infrastructure-as-a-service-iaas.aspx

    Thanks, Jason


    Didn't get enough help here? Submit a case with the Microsoft Customer Support teams for deeper investigation - Azure service support: https://manage.windowsazure.com/?getsupport=true For on Premise software support go here instead: http://support.microsoft.com/select/default.aspx?target=assistance

    Saturday, February 21, 2015 12:06 AM
  • Hi Jason,

    SQL credentials successfully added from Azure VM.

    Previously I installed the software(data gateway management s/w) and registered it in Azure VM. Then tried to set the Credentials in Azure portal from my machine.This failed.

    Setting credentials using Azure portal (Internet explorer) in Azure VM worked !!

    Is that the right way??

    Thanks, Sridevi

    • Edited by Sridevi K Monday, February 23, 2015 7:22 AM formatting
    Monday, February 23, 2015 7:21 AM
  • I will check with the product team and let you know what I find out.

    In my assumptions, I thought setting the credentials in the portal should work from any client machine.  I didn't know there was any restriction to be on the same machine (Azure VM where the Data Management Gateway lives) to do so. But maybe there is something with encrypting the credential.

    Linked Services blade > Click your Data Store for your SQL Server > click the Settings tile > Click Credentials > Click Here to set credentials.

    It does require IE or ClickOnce compatible browser. The tool runs (CredentialsManager.exe)  and prompt you for the SQL authentication or Windows Authentication.

    Thanks, Jason


    Didn't get enough help here? Submit a case with the Microsoft Customer Support teams for deeper investigation - Azure service support: https://manage.windowsazure.com/?getsupport=true For on Premise software support go here instead: http://support.microsoft.com/select/default.aspx?target=assistance

    Thursday, March 5, 2015 12:04 AM
  • I believe the gateway stores all the credentials localy and only the encrypted credentials get sent up to azure.

    so yes, I have found you have to run the credentialsmanager locally from the gateway server.

    Thursday, March 5, 2015 2:37 AM
  • I found out the CredentialsManager tool which sets the credentials when you click the link does not have to run on the same computer with Data Management Gateway, but it does have to run on a computer which is able connect to the Data Management Gateway. So you could do the credential setting on a computer in the same local network, but probably not the VM in Azure if it is outside your local corporate network.

    This is because CredentialsManager needs to talk to DMG when setting credentials.

    Thanks, Jason


    Didn't get enough help here? Submit a case with the Microsoft Customer Support teams for deeper investigation - Azure service support: https://manage.windowsazure.com/?getsupport=true For on Premise software support go here instead: http://support.microsoft.com/select/default.aspx?target=assistance

    Thursday, March 5, 2015 2:57 AM