mssql-python provides a straightforward interface to create a new connection to a database by calling theconnect() function, which returns an instance of theConnection Class. This Connection class manages all aspects of interacting with the database, including establishing a session with SQL Server, controlling transactions by committing or rolling back changes, and closing the connection when finished.

The connection string traditionally indicates the database server, the specific database to connect to, driver settings, and security details (e.g., Trusted Connection).mssql-python make it very simple to give the connection attributes to connect to the server and database.

Here is an example of the connection string that must be defined within the driver to connect to the SQL Server database:

# Using SQLPassword authenticationconn_str=Server=<your_server_name>;Database=<your_database_name>;UID=<your_user_id>;PWD=<your_password>;Trusted_Connection=yes;Encrypt=yes;TrustServerCertificate=yes;Authentication=<SqlPassword>;ApplicationName=<your_application_name_optional>;

# Using EntraID authenticationconn_str=Server=<your_server_name>;Database=<your_database_name>;Uid=your_user_id@your_domain.com;Encrypt=yes;TrustServerCertificate=yes;ConnectionTimeout=30;Authentication=ActiveDirectoryInteractive;ApplicationName=<your_application_name_optional>;

In this release, it is recommended to use only the specified attributes as other attributes have not been fully tested:

• Server: Specifies the name or network address of the SQL Server instance to connect to.

• Database: Sets the name of the database to be used after the connection is established.

• UID: The SQL Server login account (User ID).

• PWD: The password for the SQL Server login account specified in the UID parameter.

• Trusted_Connection: When set to "yes", instructs the driver to use Windows Authentication for login validation.

• Encrypt: Determines whether data should be encrypted before sending it over the network. Possible values are "yes", "no", and "strict".

• TrustServerCertificate: When used with Encrypt, enables encryption using a self-signed server certificate without validation.

• Authentication: Sets the authentication mode to use when connecting to SQL Server (SqlPassword,ActiveDirectoryPassword,ActiveDirectoryIntegrated,ActiveDirectoryInteractive,ActiveDirectoryMsi,ActiveDirectoryServicePrincipal). Themssql-python driver uses the same connection string attributes as the ODBC driver. SeeUsing Microsoft Entra ID with the ODBC Driver for more details on authentication.

• Application Name: The name of the application calling SQLDriverConnect.

• Connection Timeout: Specifies the duration (in seconds) to wait for a connection to the server before terminating the attempt and generating an error.

EntraID authentication is now fully supported on MacOS and Linux but with certain limitations as mentioned in the table:

NOTE:

• Access Token: the connection stringmust not containUID,PWD,Authentication, orTrusted_Connection keywords.
• ActiveDirectoryDeviceCode: make sure to specify aConnect Timeout that provides enough time to go through the device code flow authentication process.
• Default: Ensure you're authenticated via az login, or running within a managed identity-enabled environment.

Following are the methods and attributes exposed throughConnection Class:

Creates a new Connection object.

frommssql_pythonimportconnectconn_str="Server=<your_server_name>;Database=<your_db_name>;Trusted_Connection=yes;"conn=connect(conn_str)

Creates and returns a cursor object for executing SQL commands.

cursor=conn.cursor()cursor.execute("SELECT * FROM T1")rows=cursor.fetchall()