Not able to establish Target connection to MS SQL DB Server since server has Dynamic IP

[pavannadgoudar]

Hi Team,

Please can you let us know what is the syntax of Connection command when we want to establish connection to a DB server which has dynamic IP.

We are not able to establish Target connection to MS SQL DB Server since server has Dynamic IP and it keeps changing. So not able to run Connections command.

We successfully established connection to Source DB Server which is also MS SQL but has Static IP.

Command we are using :
data-validation connections add -c mssqlPaaS MSSQL --url "mssql+pyodbc://@>:1433/?driver=ODBC+Driver+17+for+SQL+Server&TrustServerCertificate=yes&authentication=ActiveDirectoryIntegrated"

Error :

Traceback (most recent call last):
File "C:\Users<UserName>\AppData\Local\Programs\Python\Python310\lib\runpy.py", line 196, in run_module_as_main
return run_code(code, main_globals, None,
File "C:\Users<UserName>\AppData\Local\Programs\Python\Python310\lib\runpy.py", line 86, in run_code
exec(code, run_globals)
File "C:\Users<UserName>\AppData\Local\Programs\Python\Python310\Scripts\data-validation.exe_main.py", line 7, in
File "C:\Users<UserName>\AppData\Local\Programs\Python\Python310\lib\site-packages\data_validation_main.py", line 677, in main
run_connections(args)
File "C:\Users<UserName>\AppData\Local\Programs\Python\Python310\lib\site-packages\data_validation_main.py", line 639, in run_connections
_ = clients.get_data_client(conn)
File "C:\Users<UserName>\AppData\Local\Programs\Python\Python310\lib\site-packages\data_validation\clients.py", line 288, in get_data_client
raise exceptions.DataClientConnectionFailure(msg)
data_validation.exceptions.DataClientConnectionFailure: Connection Type "MSSQL" could not connect: (pyodbc.OperationalError) ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: The wait operation timed out.\r\n (258) (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. (258)')
(Background on this error at: https://sqlalche.me/e/14/e3q8)

@nehanene15 @nj1973 @helensilva14

[helensilva14]

Hi @pavannadgoudar!

At first, I don't think it's possible to connect with Dynamic IP address, but I was doing some search and you can try to connect passing the server/machine name.

Links I saw:

• How to connect to sql server with dynamic IP Address
• https://learn.microsoft.com/en-us/sql/connect/python/pyodbc/step-3-proof-of-concept-connecting-to-sql-using-pyodbc?view=sql-server-ver16#connect-and-query-data

[pavannadgoudar]

Thanks for your response.

Source Connection : We are able to establish connection. This is static IP and MS SQL Database.
Target Connection : We are not able to establish connection. This is dynamic IP and Azure SQL Database.

Target Connection Error details:

We were getting below error. We are trying to connect to Azure SQL instance. Is Azure SQL instance supported? We are able to connect to this instance from Microsoft SSMS.

Can we connect to Azure SQL with same connection parameter "MSSQL"? If not what parameter we can use.

Command :
data-validation connections add -c mssqlPaaS MSSQL --url "mssql+pyodbc://@:1433/?driver=ODBC+Driver+17+for+SQL+Server&TrustServerCertificate=yes&authentication=ActiveDirectoryIntegrated"

Error Message:
raise exceptions.DataClientConnectionFailure(msg)
data_validation.exceptions.DataClientConnectionFailure: Connection Type "MSSQL" could not connect: (pyodbc.Error) ('HY000', '[HY000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot open server "" requested by the login. The login failed. (40532) (SQLDriverConnect); [HY000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot open server "" requested by the login. The login failed. (40532)')
(Background on this error at: https://sqlalche.me/e/14/dbapi)

@nehanene15 @nj1973 @helensilva14

[pavannadgoudar]

@nehanene15 @nj1973 @helensilva14 Hi All please help us with this. once we establish connection we wish to move to Prod. Appreciate your support.

[nj1973]

Hi @pavannadgoudar, while I haven't tested with Azure myself I'm sure it will be possible. In your example you are not specifying a server name.

There are some example connection urls here:
https://pydoc.dev/sqlalchemy/latest/sqlalchemy.dialects.mssql.pyodbc.html

Also in this Microsoft document there is an Azure example url: https://learn.microsoft.com/en-us/azure/azure-sql/database/azure-sql-python-quickstart?view=azuresql&tabs=windows%2Csql-auth#configure-the-local-connection-string

Notice the requirement to include a <database-server-name>. I believe you still have this regardless of dynamic ip.

'Driver={ODBC Driver 18 for SQL Server};Server=tcp:<database-server-name>.database.windows.net,1433;Database=<database-name>;UID=<user-name>;PWD=<user-password>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30'

Perhaps you could test with pyodbc directly to prove connectivity before using the url in DVT? That way you will be able to tell the difference between a DVT shortcoming and an incorrect url.

[pavannadgoudar]

@nj1973 Thank you for above inputs. I will try with above options and update.