none
Microsoft R services + client connecting sql server issue. RRS feed

  • Question

  • HI All

    We are configuring advance analytics platform in azure , we have configured VM with R (in-database service ) on sql server 2016 image vm and now when we are configuring datascience client in azure VM and while defining sql server compute context , we are facing the fallowing error .

    rxGetVarInfo(data = inDataSource)
    [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
    [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).

    Could not open data source.

    [Microsoft][ODBC Driver Manager] Connection not open

    ODBC Error in SQLDisconnect.

    the SQL account whihc we are pasing in sqlconnection is having db_onwer access on the databse on server level .

    the client connection to server is working fine because the we are able to load data  into sql server from client as per the steps mentioned in RunSQL_R_Walkthrough.ps1  ( microsoft tutorial).

    Please let me  know what would be the issue for this error


    Surendra Thota

    Wednesday, April 12, 2017 4:04 PM

All replies

  • Hi Surendra,

    What does the inDataSource look like in term of connection string. FRom what you wrote above, it seems to me it fails in the rxGetVarInfo call, and in that call you have defined the data source. The data source should have  connection string - you sure there is nothing wrong there?

    Niels


    http://www.nielsberglund.com | @nielsberglund

    Thursday, April 13, 2017 1:48 AM
  • Dear Niels 

    Thanks for your response , Previous i have used connection str with sql account  like below

    "Driver=SQL Server;Server= TSTEUNBISQLIS01;Database= R_Client_Conn;Uid= R_TestSqluser2;Pwd= XXXXXXXX"

    But now i have tried with windows authentication like this 

    connStrWin <- "Driver=SQL Server;Server=TSTEUNBISQLIS01;Database=R_Client_Conn;Trusted_Connection=Yes" 

    Now it is working fine . my doubt why it is not working sql user , where sql user had full access n that db. 

    and inthe tutotrial for sql authentication , for server name they are asking 

    Your_Server_Name.somedomain.com

     # SQL authentication  
     connStr <- "Driver=SQL Server;Server=Your_Server_Name.somedomain.com;Database=Your_Database_Name;Uid=Your_User_Name;Pwd=Your_Password"

    for windows authentication it is 

    # Windows authentication  
    connStrWin <- "Driver=SQL Server;Server=SQL_instance_name;Database=database_name;Trusted_Connection=Yes" 

    Here it is not mention that domain . 


    Surendra Thota

    Thursday, April 13, 2017 11:50 AM
  • Hi Surendra,

    You write:

    "Previous i have used connection str with sql account  like below

    "Driver=SQL Server;Server= TSTEUNBISQLIS01;Database= R_Client_Conn;Uid= R_TestSqluser2;Pwd= XXXXXXXX"

    Is that the connection string you are using when trying rxGetVarInfo(data = inDataSource)? 

    You also wrote in your original post that you had managed to load data into SQL Server using the steps in a tutorial. Was that also on Azure, and what did the connection string look like?

    Niels


    http://www.nielsberglund.com | @nielsberglund

    Thursday, April 13, 2017 4:07 PM
  • Hi Niels

    Yes it is the connection whihc is used in inDataSource . it is working when i used windows authentication but getting that error when i used SQL authentication . Here is the tutotial link  

    https://docs.microsoft.com/en-us/sql/advanced-analytics/r-services/lesson-1-prepare-the-data-data-science-end-to-end-walkthrough

    Both server and datascience client VMs are in azure.


    Surendra Thota

    Thursday, April 13, 2017 5:02 PM
  • Hi,

    So unfortunately I do not know that much about SQL Server VM's in Azure. However, there may be two options:

    1. Your credentials are not correct when using SQL authentication. Log onto the actual box SQL runs on and try to log onto the SQL instance. That should tell you if the credentials are OK.
    2. Maybe, when authenticating with SQL auth in Azure, you need to define the domain as in the tutorial. Try that and see what happens.

    Below follows a couple of URL's about security etc., for SQL VM's in Azure, maybe they can help:

    • https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-portal-sql-server-provision
    • https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-security

    Let us know how it goes.

    Niels


    http://www.nielsberglund.com | @nielsberglund

    Thursday, April 13, 2017 5:41 PM