Connecting Python to Remote SQL Server

Question

I am trying to connect Python to our remote SQL Server but I am not getting it. Following is a code that I used.

 server = 'server,1433' 
 database = 'db' 
 username = 'username' 
 password = 'pw' 
 cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
 cursor = cnxn.cursor()
 cursor.execute('SELECT top 1 * FROM db.dbo.t_location')
 for row in cursor:
     print(row)

We have 2 servers. One is database server but I use application server for SQL which connects to database server. This is the error I'm getting. I am trying for a week but I'm not sure what am I missing here. Any help would be appreciated

OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: No such host is known.\r\n (11001) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (11001)')

ADDED:

connection_str = ("Driver={SQL Server Native Client 11.0};"
                  "Server= 10.174.124.12,1433;"
                  #"Port= 1433;"
                  "Database=AAD;"
                  "UID=dom\user;"
                  "PWD=password;"
                  )

connection = pyodbc.connect(connection_str) data = pd.read_sql("select top 1 * from dbo.t_location with (nolock);",connection)

I used the above code and now I see this error. Seems like it worked but failed to login. Usually I have to use Windows authentication in SSMS once I put my credentials to login in remote desktop.

('28000', "[28000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'dom\user'. (18456) (SQLDriverConnect); [28000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'dom\user'. (18456)")

Answer:

I am excited that I finally found a solution using pymssql. I don't know pyodbc wasn't working but I am sure I must have had done something wrong. I used below code to get the data from remote SQL server using Python.

import pymssql

conn = pymssql.connect(
    host=r'10.174.124.12',
    user=r'dom\user',
    password=r'password',
    database='db'
)
cursor = conn.cursor(as_dict=True)
cursor.execute('Select top 4 location_id, description from t_location with (nolock)')
data = cursor.fetchall()
data_df = pd.DataFrame(data)

cursor.close()

Ignore my code at this moment. I still have to do some cleaning but this code will work.

The important part of the error message is: Server is not found or not accessible. Check the connection string you dynamically generate FIRST and verify that it has the correct information. If that is correct, then try the connection troubleshooter — SMor
– SMor, Commented Mar 22, 2021 at 21:02
While you are debugging, STOP using 3 part names in your queries. The connection should determine the database. Using 3 part names (e.g, DB.SCHEMA.TABLE) makes your code harder to move to different environments. — SMor
– SMor, Commented Mar 22, 2021 at 21:04
"We have 2 servers. One is database server but I use application server for SQL which connects to database server." - It sounds like you are trying to connect to the wrong server. For a direct database connection you need to connect to the machine where the database server software is running. — Gord Thompson
– Gord Thompson, Commented Mar 22, 2021 at 21:35

hkay · Accepted Answer · 2021-03-23 17:05:07Z

6

Finally to answer my question, I had to use pymssql which worked. I did not have to put the port number which was making me confused. Thanks everyone for taking out time to answer.

import pymssql

conn = pymssql.connect(
    host=r'10.174.124.12',
    user=r'dom\user',
    password=r'password',
    database='db'
)
cursor = conn.cursor(as_dict=True)
cursor.execute('Select top 4 location_id, description from t_location with (nolock)')
data = cursor.fetchall()
data_df = pd.DataFrame(data)

cursor.close()

answered Mar 23, 2021 at 17:05

hkay

1882 gold badges3 silver badges20 bronze badges

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

Comments

Abdulmohsen Almasoud · Accepted Answer · 2021-03-22 20:56:01Z

2

you can use this function :

def connectSqlServer(Server , Database , Port , User , Password):
    try:
        conn = pyodbc.connect('Driver={SQL Server}; Server='+Server+'; 
Database='+Database+'; Port='+Port+'; UID='+User+'; PWD='+Password+';')
        cursor = conn.cursor()


except Exception as e:
    print("An error occurred when connecting to DB, error details: {}".format(e))
    return False, None
else:
    return True, cursor

answered Mar 22, 2021 at 20:56

Abdulmohsen Almasoud

666 bronze badges

4 Comments

hkay Over a year ago

Hi @Abdulmohsen I tried your function but got the same error. Is there any specific procedure that I should keep in mind while trying to connect to remote SQL server?

Abdulmohsen Almasoud Over a year ago

@HabibKhan Hi, can you connect to your DB using ssms ? it seems a network-related issue most likely the port 1433 is not opened to your DB server.

hkay Over a year ago

Thanks for responding. Really appreciate it. Yes when I open SSMS it seems to be working fine. I think the problem is may be with port or firewall. I will check around and see if I can find anything else.

MarmiK Over a year ago

return type is Touple so once use it like cursor = connectSqlServer(.,"Adventureworks2019","1433","User","Pass")[1]

Collectives™ on Stack Overflow

Connecting Python to Remote SQL Server

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related