Arun Arun - 1 day ago 5
R Question

SQL Server ODBC connection failed

I am trying to connect SQL server using ODBC.

Could some one help interpreting what this error is and how this can be rectified?

Kindly note that there are no password issues as I use the same credentials to connect to the SQL server using Aqua studio.

dbhandle <- odbcDriverConnect('driver={SQL Server};server=SQLBBAQA;database=bbadb;uid = "aaa_bbb_ccc", pwd = "aaabbbccc123&" ')


Warning messages:
1: In odbcDriverConnect("driver={SQL Server};server=SQLBBAQA;database=bbadb;uid = \"aaa_bbb_ccc\", pwd = \"aaabbbccc123&\" ") :
[RODBC] ERROR: state 08001, code 17, message [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
2: In odbcDriverConnect("driver={SQL Server};server=SQLBBAQA;database=bbadb;uid = \"aaa_bbb_ccc\", pwd = \"aaabbbccc123&\" ") :
[RODBC] ERROR: state 01000, code 2, message [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
3: In odbcDriverConnect("driver={SQL Server};server=SQLBBAQA;database=bbadb;uid = \"aaa_bbb_ccc\", pwd = \"aaabbbccc123&\" ") :
[RODBC] ERROR: state 01S00, code 0, message [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute
4: In odbcDriverConnect("driver={SQL Server};server=SQLBBAQA;database=bbadb;uid = \"aaa_bbb_ccc\", pwd = \"aaabbbccc123&\" ") :
ODBC connection failed

erg erg
Answer

I see multiple mistakes in the connection string:

server=SQLRAPQA should be in the form server=MACHINE\INSTANCE. Use server=.\SQLRAPQA if the instance is located on the same machine.

Remove all whitespaces.

Use ; as separator, not ,.

As referenced by zx8754, in RODBC odbcDriverConnect() Connection Error it is shown that a connection string should look like:

'driver={SQL Server};server=servername\\instancename,port;database=testing;uid=abc;pwd=123456' . Note the double occurences of \, this seems to be rodbc specific.

Comments