python pyodbc : how to connect to a specific instance

Question

Am trying to connect to a specific instance of SQL Server and get some data from system tables. Am connecting using this code snippet:

connSqlServer = pyodbc.connect('DRIVER={SQL Server Native Client 10.0};SERVER=192.106.0.102;DATABASE=master;INSTANCE=instance1;UID=sql2008;PWD=password123;Trusted_Connection=yes')
...
cursorObj.execute("select * from sys.dm_os_sys_info")
row = cursorObj.fetchone()
print("rows from table ",row) 

however am getting the values for the default instance only, but not able to get the value for 'instance1'. So, giving instance name in 'INSTANCE=instance1' really seems to have no effect. Even without it (tried giving 'PORT=1443', the instance's port number), am getting the values only for the default SQL Server instance. How to force it to get the values for the specific instance?

Plainclothesman

Answer 1

Authentication

First, you're providing both uid/pwd (SQL Server authentication) and trusted_connection (Windows authentication). Pick one, you can't use both. I'll assume SQL Server authentication for the following examples.

Connection strings

Connecting to named instance instance1 using the instance name:

connSqlServer = pyodbc.connect('DRIVER={SQL Server Native Client 10.0};SERVER=192.106.0.102\instance1;DATABASE=master;UID=sql2008;PWD=password123')

Connecting to named instance using TCP/IP using the port number 1443:

connSqlServer = pyodbc.connect('DRIVER={SQL Server Native Client 10.0};SERVER=192.106.0.102,1443;DATABASE=master;UID=sql2008;PWD=password123')

Keyword alternative

pyodbc.connect() supports keywords, I think these are easier to read and you don't have to do any string formatting if you're using variables for connection string attributes:

Named instance:

connSqlServer = pyodbc.connect(driver='{SQL Server Native Client 10.0}',
                               server='192.106.0.102\instance1',
                               database='master',
                               uid='sql2008',pwd='password123')

TCP/IP port:

connSqlServer = pyodbc.connect(driver='{SQL Server Native Client 10.0}',
                               server='192.106.0.102,1443',
                               database='master',
                               uid='sql2008',pwd='password123')

Lyssa

This is "old" but I am commenting here because I am having different results. When I try 192.106.0.102\instance1 in the connection string, my login times out because the server cannot be found (of course I am using values correct for me). I have seraching low and high and forthe life of me I cannot find out how to connect to a named instance. The "INSTANCE" keyword in the connection string doesn't do anything for me either. – Nehemiah

@Nehemiah Please ask a new question with your code (including connection string) and error message. The instance attribute above was invalid and has been removed. – Lyssa

Thanks - in fact, I do have a new question up already. #30363848 – Nehemiah

I found I had to enter an additional slash to escape the slash for the instance. Worked great after that – Pointing