Connection to SQL Database

Connecting to SQL Server Database

mssql-python provides a straightforward interface to create a new connection to a database by calling the connect() function, which returns an instance of the Connection 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.

Connection String

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

# Using SQLPassword authentication
conn_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>;

# Using EntraID authentication
conn_str = Server=<your_server_name>;Database=<your_database_name>;Uid=your_user_id@your_domain.com;Encrypt=yes;TrustServerCertificate=yes;Authentication=ActiveDirectoryInteractive;

Supported connection string parameters

Parameter	Category	Description
Server, Address, Addr	Server Identification	Follows the data source format. Can be a hostname, IP address, or hostname with port, named pipe etc (e.g., localhost, 192.168.1.1, server.example.com,1433)
UID	Authentication	User ID for SQL Server authentication
PWD	Authentication	Password for SQL Server authentication
Authentication	Authentication	Sets the authentication mode to use when connecting to SQL Server (SqlPassword, ActiveDirectoryPassword, ActiveDirectoryIntegrated, ActiveDirectoryInteractive, ActiveDirectoryMsi, ActiveDirectoryServicePrincipal). The mssql-python driver uses the same connection string attributes as the ODBC driver. See Using Microsoft Entra ID with the ODBC Driver for more details on authentication.
Trusted_Connection	Authentication	When set to yes, uses Windows Authentication instead of SQL Server authentication
Database	Database	Specifies the initial database to connect to
Encrypt	Encryption & Security	Controls connection encryption. Values: yes (mandatory), no (optional), strict (TLS 1.3+ enforced)
TrustServerCertificate, Trust_Server_Certificate	Encryption & Security	When set to yes, the driver trusts the server certificate without validation (useful for self-signed certificates)
HostnameInCertificate	Encryption & Security	Specifies the hostname to validate against the server certificate
ServerCertificate	Encryption & Security	Specifies the path to the server certificate file for validation
ServerSPN	Encryption & Security	Specifies the Service Principal Name (SPN) of the server for Kerberos authentication
MultiSubnetFailover	Connection Behavior	When set to yes, enables faster detection of and connection to the currently active server in AlwaysOn Availability Groups
ApplicationIntent	Connection Behavior	Specifies the application workload type when connecting to an AlwaysOn Availability Group. Values: ReadWrite (default), ReadOnly
ConnectRetryCount	Connection Behavior	Number of reconnection attempts for broken connections (default: 1, range: 0-255)
ConnectRetryInterval	Connection Behavior	Time in seconds between reconnection attempts (default: 10, range: 1-60)
KeepAlive	Keep-Alive	TCP keep-alive time in seconds (default: 30)
KeepAliveInterval	Keep-Alive	TCP keep-alive interval in seconds between keep-alive packets (default: 1)
IpAddressPreference	Connection Behavior	Specifies IP address preference when resolving hostnames. Values: IPv4First, IPv6First, UsePlatformDefault
Packet Size, PacketSize	Performance	Network packet size in bytes (range: 512-32767, default: 4096)

Entra ID authentication is now fully supported on Windows, macOS, and Linux. Platform-specific requirements are outlined in the table below:

Authentication Method	Windows Support	macOS/Linux Support	Notes
ActiveDirectoryPassword	✅ Yes	✅ Yes	Username/password-based authentication
ActiveDirectoryInteractive	✅ Yes	✅ Yes	Interactive login via browser; requires user interaction
ActiveDirectoryMSI (Managed Identity)	✅ Yes	✅ Yes	For Azure VMs/containers with managed identity
ActiveDirectoryServicePrincipal	✅ Yes	✅ Yes	Use client ID and secret or certificate
ActiveDirectoryIntegrated	✅ Yes	✅ Yes	Requires Kerberos/SSPI configuration; Kerberos setup needed on Linux/macOS
ActiveDirectoryDeviceCode	✅ Yes	✅ Yes	Device code flow for authentication; suitable for environments without browser access
ActiveDirectoryDefault	✅ Yes	✅ Yes	Uses default authentication method based on environment and configuration

For more details on these authentication methods, refer to the Microsoft Entra ID support documentation

Following are the methods and attributes exposed through Connection Class:

connect() Method

Creates a new Connection object.

from mssql_python import connect

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

cursor() Method

Creates and returns a cursor object for executing SQL commands.

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