SQL Server ODBC connection failed

Question

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

Put `server` and `database` values in quotes. Like `server="1234adsd"` — CuriousBeing, Feb 15 '16 at 09:41
@MaxPD - Thanks for your reply. I tried using that. But it fails again — Arun, Feb 15 '16 at 09:48
Maybe [this one](http://stackoverflow.com/questions/15420999/rodbc-odbcdriverconnect-connection-error) is relevant? — zx8754, Feb 15 '16 at 10:12
A handy site for connection strings: https://www.connectionstrings.com/sql-server/ — Liesel, Feb 15 '16 at 10:28

score 4 · Accepted Answer · edited May 23 '17 at 12:09

4

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.

edited May 23 '17 at 12:09

Community

1
1

answered Feb 15 '16 at 10:26

erg

1,632
1
11
23

1

Actually `server=.\SQLRAPQA` is the named instance `SQLRAPQA` on the local machine, `server=SQLRAPQA` is the default instance on the machine `SQLRAPQA` Instance name is not used if you specify the port. – Liesel Feb 15 '16 at 10:32
@erg this has worked. I have inserted the instance name and port. The database has responded. Thanks a lot for your help. – Arun Feb 17 '16 at 14:43

SQL Server ODBC connection failed

1 Answers1