How to connect to sql server 2008 via an IP address using pyodbc in Python

Question

I am able to connect to server with mssql management studio but not able to connect using python I think some problem in connection string please help below is string I am using.

import pyodbc as p

connStr = ( r'DRIVER={SQL Server};Server=ip; Network=DBMSSOCN;Initial Catalog=' + database + ';User ID=' + id +';Password=' + pass1 +';Trusted_Connection=True' +';')

conn = p.connect(connStr)

error is like below

 conn = p.connect(connStr)
pyodbc.Error: ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][TCP/IP Sock
ets]SQL Server does not exist or access denied. (17) (SQLDriverConnectW); [01000
] [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()).
 (10060); [01S00] [Microsoft][ODBC SQL Server Driver]Invalid connection string a
ttribute (0)')

Has SQL server been configured to listen on TCP sockets? By default it doesn't. You'd have to go into the Configuration Manager to check — Marc B
– Marc B, Commented Jan 10, 2011 at 13:01
thanks for ur reply Marc.. yes it is listening on port 1433..which is i am using in ip — sagar
– sagar, Commented Jan 10, 2011 at 13:36
is anyone connected using ip to sql server 2008..i found very few documentation about this — sagar
– sagar, Commented Jan 11, 2011 at 7:25

Zach Brantmeier · Accepted Answer · 2022-05-28 11:53:21Z

8

After lots of trial and error this string ended up working:

connStr = ('DRIVER={SQL Server Native Client 10.0};Server=ip;port=port;Network Library=DBMSSOCN;Database=TEST;uid=id;pwd=pass;')

edited May 28, 2022 at 11:53

Zach Brantmeier

7463 gold badges9 silver badges24 bronze badges

answered Jan 11, 2011 at 8:32

sagar

1,5056 gold badges22 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mr.Wizard · Accepted Answer · 2011-08-18 19:53:14Z

There are actually two or three SQL Server drivers written and distrubuted by Microsoft: one referred to as "SQL Server" and the other as "SQL Native Client" and "SQL Server Native Client 10.0}".

DRIVER={SQL Server};SERVER=cloak;DATABASE=test;UID=user;PWD=password

DRIVER={SQL Native Client};SERVER=dagger;DATABASE=test;UID=user;PWD=password

DRIVER={SQL Server Native Client 10.0};SERVER=dagger;DATABASE=test;UID=user;PWD=password

The "SQL Server" one works for all versions of SQL Server, but only enables features and data types supported by SQL Server 2000, regardless of your actual server version.

For SQL Server 2005 installations, use "SQL Native Client" to enable 2005 features and types. Note that this version is not provided in all SQL Server 2008 installations!

Finally, you need "SQL Server Native Client 10.0" for SQL Server 2008 features and types.

Collectives™ on Stack Overflow

How to connect to sql server 2008 via an IP address using pyodbc in Python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related