1

i am trying to connect to SQL Server database (which is located in a VM, but the user i am using is db_owner and can connect without being inside the VM) to Query the database and get a result back. What does the script do is to check if the database is scrambled.

import pandas as pd
import pyodbc
import cx_Oracle
import csv

# Load the data from the Excel file
df = pd.read_excel('DatabasesList.xlsx')
print (df)

cx_Oracle.init_oracle_client(lib_dir=r"C:\oracle\instantclient_21_10")
first_name_to_check = 'XXXX'

# Open the CSV file
with open('results.csv', 'w', newline='') as file:
    writer = csv.writer(file)
    # Write the headers
    writer.writerow(['Database', 'Host', 'Result'])

    # Loop over the rows of the dataframe
    for _, row in df.iterrows():
        username = row['Username']
        password = row['Password']
        database = row['Database']
        host = row['Host']
        rdbms = row['Rdbms']

        conn = None
        # Connect to the database depending on the RDBMS
        if rdbms == 'DB2':
            continue
        elif rdbms == 'MsSql':
            conn_str = f"DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={host};DATABASE={database};UID={username};PWD={password};"
            try:
                conn = pyodbc.connect(conn_str)
            except pyodbc.OperationalError:
                print(f"Could not connect to {host} - {database}. Moving to next database.")
                continue
        else:
            print(f"No connection process implemented for {rdbms} - {database} - {host}.")
            continue

        print("Executing in: ", database)
        if conn is not None:
            # You can now use `conn` to query the database
            cursor = conn.cursor()
            cursor.execute(f"SELECT COUNT(*) FROM CUSTOMER WHERE FIRST_NAME LIKE '%{first_name_to_check}%'")

            # Fetch the result
            result = cursor.fetchone()[0]

            # Write the results to the CSV file
            writer.writerow([database, host, result])

            # Close the connection
            cursor.close()
            conn.close()

The error Occurs in the Try which the error message is:

('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [1326]. (1326) (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. (1326)')

Note that this happens for every entry in the Dataframe.

Trusted_Connection = Yes; did not work.

A dataframe looks like: Dataframe Example

Thom A
  • 88,727
  • 11
  • 45
  • 75
Vasileios G
  • 19
  • 6
  • Does this answer your question? [Cannot Connect to Server - A network-related or instance-specific error](https://stackoverflow.com/questions/18060667/cannot-connect-to-server-a-network-related-or-instance-specific-error) – Thom A Jul 31 '23 at 11:50
  • No, i have done all the steps this question depicts. – Vasileios G Jul 31 '23 at 12:06
  • 2
    Then I would suggest it's a network issue, and the host you are running python on cannot communicate with the remote host that SQL Server is running on. – Thom A Jul 31 '23 at 12:08
  • is there any way to perform a test to verify? – Vasileios G Jul 31 '23 at 12:12
  • 1
    @VasileiosG, try testing port 1433 connectivity. PowerShell example from a command prompt: `powershell -Command "tnc YourHostName -Port 1433"` – Dan Guzman Jul 31 '23 at 12:25
  • 1
    So, out of curiosity... are you trying to use Microsoft SQL Server drivers to connect to an Oracle database server? – AlwaysLearning Jul 31 '23 at 12:37
  • no no, in the oracle i have used "Pass" because i have not yet implemented it. I will put another ELIF statement to point on Oracle databases, but i need to overcome the SQL Server first. – Vasileios G Jul 31 '23 at 12:38
  • Pritn out your connection string. Are you sure you want to use named pipes – siggemannen Jul 31 '23 at 13:13
  • Did you try `Server:` or `Server:`? Are you sure the service is running, is running on port 1433 and is not a named instance, remote connections are allowed, and that TCP/IP is enabled in SQL Server Configuration Manager? – Stuck at 1337 Jul 31 '23 at 13:25
  • @Stuckat1337 Service is running, Running on port 1433, remote connections allowed, TCP is enabled. What do you mean by Named Instance? it has its own name. – Vasileios G Jul 31 '23 at 18:13
  • @siggemannen yes, named pipes help me do it for multiple databases and servers. – Vasileios G Jul 31 '23 at 18:14
  • If you installed a named instance then it should be `SERVER={host}\{instance name}` and in that case the SQL Server browser service needs to be running (and SQL Server itself probably is _not_ running on 1433 - but you can check with `Test-NetConnection`). Or please elaborate on what you mean by "it has its own name." – Stuck at 1337 Jul 31 '23 at 18:51
  • @Stuckat1337 it is as you said, Server={HOST}\{INSTANE_NAME}. SQL Server Browser service is up and running too. – Vasileios G Aug 01 '23 at 05:15

0 Answers0