0
# Connection Configuration
1

2
Comprehensive connection management with support for various authentication methods, SSL/TLS encryption, connection pooling, and SQL Server-specific features. Covers both high-level and low-level connection configuration options.
3

4
## Capabilities
5

6
### Basic Connection Parameters
7

8
Core connection parameters supported by both `pymssql.connect()` and `_mssql.connect()`.
9

10
```python { .api }
11
def connect(
12
    server='.', 
13
    user=None, 
14
    password=None, 
15
    database='', 
16
    timeout=0, 
17
    login_timeout=60, 
18
    charset='UTF-8', 
19
    appname=None, 
20
    port='1433'
21
) -> Connection:
22
    """
23
    Basic connection parameters.
24
    
25
    Parameters:
26
    - server (str): Database server and instance, default '.' (local)
27
    - user (str): Database username
28
    - password (str): User password  
29
    - database (str): Database name to connect to
30
    - timeout (int): Query timeout in seconds, 0 = no timeout
31
    - login_timeout (int): Connection timeout in seconds, default 60
32
    - charset (str): Character encoding, default 'UTF-8'
33
    - appname (str): Application name shown in SQL Server
34
    - port (str): TCP port number, default '1433'
35
    """
36
```
37

38
### Advanced Connection Options
39

40
Extended configuration options for specialized use cases.
41

42
```python { .api }
43
def connect(
44
    # ... basic parameters ...
45
    as_dict=False,
46
    autocommit=False, 
47
    tds_version=None,
48
    use_datetime2=False,
49
    arraysize=1,
50
    conn_properties=None,
51
    host='',
52
    encryption=None,
53
    read_only=False
54
) -> Connection:
55
    """
56
    Advanced connection parameters.
57
    
58
    Parameters:
59
    - as_dict (bool): Return rows as dictionaries instead of tuples
60
    - autocommit (bool): Enable autocommit mode
61
    - tds_version (str): TDS protocol version ('7.0', '7.1', '7.2', '7.3')
62
    - use_datetime2 (bool): Use DATETIME2 compatible conversion
63
    - arraysize (int): Default cursor arraysize for fetchmany()
64
    - conn_properties (str|list): SQL commands to execute on connection
65
    - host (str): Alternative server specification format
66
    - encryption (str): SSL/TLS mode ('off', 'request', 'require')
67
    - read_only (bool): Connect in read-only mode (SQL Server 2012+)
68
    """
69
```
70

71
### Connection Properties
72

73
SQL commands executed automatically upon connection establishment.
74

75
```python { .api }
76
# Default connection properties (applied automatically)
77
DEFAULT_CONN_PROPERTIES = [
78
    'SET ARITHABORT ON',
79
    'SET CONCAT_NULL_YIELDS_NULL ON', 
80
    'SET ANSI_NULLS ON',
81
    'SET ANSI_NULL_DFLT_ON ON',
82
    'SET ANSI_PADDING ON',
83
    'SET ANSI_WARNINGS ON',
84
    'SET CURSOR_CLOSE_ON_COMMIT ON',
85
    'SET QUOTED_IDENTIFIER ON',
86
    'SET TEXTSIZE 2147483647'
87
]
88
```
89

90
### Global Configuration
91

92
Module-level functions for connection management.
93

94
```python { .api }
95
def get_max_connections() -> int:
96
    """
97
    Get maximum number of simultaneous connections allowed.
98
    
99
    Returns:
100
    Maximum connection count (default: 25)
101
    """
102

103
def set_max_connections(number: int) -> None:
104
    """
105
    Set maximum number of simultaneous connections allowed.
106
    
107
    Parameters:
108
    - number (int): Maximum connections (minimum: 1)
109
    """
110

111
def get_dbversion() -> str:
112
    """
113
    Get FreeTDS library version information.
114
    
115
    Returns:
116
    Version string (e.g., "freetds v1.2.5")
117
    """
118

119
def set_wait_callback(callback) -> None:
120
    """
121
    Set callback for cooperative multitasking support.
122
    
123
    Parameters:
124
    - callback: Function with signature callback(read_fileno)
125
    """
126

127
def version_info() -> str:
128
    """
129
    Get comprehensive version information for debugging.
130
    
131
    Returns:
132
    Version information string including pymssql, FreeTDS, Python, and OS
133
    """
134
```
135

136
## Usage Examples
137

138
### Basic Connections
139

140
```python
141
import pymssql
142

143
# Local SQL Server with Windows Authentication
144
conn = pymssql.connect(server='.', database='mydb')
145

146
# Remote SQL Server with SQL Authentication  
147
conn = pymssql.connect(
148
    server='192.168.1.100',
149
    user='myuser',
150
    password='mypassword', 
151
    database='mydb'
152
)
153

154
# Named instance on specific port
155
conn = pymssql.connect(
156
    server='MYSERVER\\SQLEXPRESS',
157
    user='sa',
158
    password='password',
159
    database='testdb'
160
)
161

162
# TCP/IP with custom port
163
conn = pymssql.connect(
164
    server='myserver.company.com,1433',
165
    user='appuser',
166
    password='apppass',
167
    database='proddb'
168
)
169
```
170

171
### Azure SQL Database
172

173
```python
174
import pymssql
175

176
# Azure SQL Database connection
177
conn = pymssql.connect(
178
    server='myserver.database.windows.net',
179
    user='username@myserver',  # Include server name in username
180
    password='password',
181
    database='mydatabase',
182
    encryption='require',      # Force SSL encryption
183
    tds_version='7.3'         # Use latest TDS version
184
)
185

186
# Azure with connection string format
187
conn = pymssql.connect(
188
    host='myserver.database.windows.net',
189
    user='username',
190
    password='password', 
191
    database='mydatabase',
192
    port='1433',
193
    encryption='require'
194
)
195
```
196

197
### High-Performance Configuration
198

199
```python
200
import pymssql
201

202
# Optimized for bulk operations
203
conn = pymssql.connect(
204
    server='localhost',
205
    user='sa',
206
    password='password',
207
    database='warehouse',
208
    autocommit=True,           # Reduce transaction overhead
209
    timeout=300,               # 5 minute query timeout
210
    arraysize=1000,           # Large fetch size
211
    conn_properties=[
212
        'SET ARITHABORT ON',
213
        'SET ANSI_WARNINGS OFF',  # Reduce warning messages
214
        'SET TEXTSIZE 2147483647',
215
        'SET LOCK_TIMEOUT 30000'  # 30 second lock timeout
216
    ]
217
)
218

219
# Configure bulk copy page size
220
conn.bulk_copy_executemany_page_size = 10000
221
```
222

223
### Development and Debugging
224

225
```python
226
import pymssql
227
from pymssql import _mssql
228

229
# Enable query debugging
230
conn = _mssql.connect(
231
    server='localhost',
232
    user='dev',
233
    password='devpass',
234
    database='testdb'
235
)
236
conn.debug_queries = True  # Print all queries to stderr
237

238
# Connection with custom application name for monitoring
239
conn = pymssql.connect(
240
    server='localhost',
241
    user='dev',
242
    password='devpass',
243
    database='testdb',
244
    appname='MyApp-Development',
245
    timeout=10                # Short timeout for development
246
)
247

248
# Custom message handler for server messages
249
def message_handler(msgstate, severity, srvname, procname, line, msgtext):
250
    print(f"Server message: {msgtext} (severity: {severity})")
251

252
conn_low = _mssql.connect('localhost', 'dev', 'devpass', 'testdb')
253
conn_low.set_msghandler(message_handler)
254
```
255

256
### Connection Pooling Pattern
257

258
```python
259
import threading
260
import queue
261
import pymssql
262

263
class ConnectionPool:
264
    def __init__(self, max_connections=10, **conn_params):
265
        self.max_connections = max_connections
266
        self.conn_params = conn_params
267
        self.pool = queue.Queue(maxsize=max_connections)
268
        self.lock = threading.Lock()
269
        
270
        # Pre-populate pool
271
        for _ in range(max_connections):
272
            conn = pymssql.connect(**conn_params)
273
            self.pool.put(conn)
274
    
275
    def get_connection(self):
276
        return self.pool.get()
277
    
278
    def return_connection(self, conn):
279
        self.pool.put(conn)
280

281
# Usage
282
pool = ConnectionPool(
283
    max_connections=25,
284
    server='localhost',
285
    user='pooluser',
286
    password='poolpass',
287
    database='mydb',
288
    autocommit=True
289
)
290

291
# Use connection from pool
292
conn = pool.get_connection()
293
try:
294
    cursor = conn.cursor()
295
    cursor.execute("SELECT * FROM users")
296
    # ... process results ...
297
finally:
298
    pool.return_connection(conn)
299
```
300

301
### TDS Version Configuration
302

303
```python
304
import pymssql
305

306
# TDS version selection for compatibility
307
versions = {
308
    'legacy': '7.0',    # SQL Server 7.0 / 2000
309
    'standard': '7.1',  # SQL Server 2000 SP1+
310
    'modern': '7.2',    # SQL Server 2005+
311
    'latest': '7.3'     # SQL Server 2008+
312
}
313

314
# Connect with specific TDS version
315
conn = pymssql.connect(
316
    server='legacy-server',
317
    user='user',
318
    password='pass',
319
    database='olddb',
320
    tds_version=versions['legacy']
321
)
322

323
# Check negotiated TDS version
324
from pymssql import _mssql
325
conn_low = _mssql.connect('server', 'user', 'pass', 'db', tds_version='7.3')
326
print(f"TDS version: {conn_low.tds_version}")
327
print(f"TDS version tuple: {conn_low.tds_version_tuple}")
328
```
329

330
### Connection String Patterns
331

332
```python
333
import pymssql
334

335
# Different server specification formats
336
connections = {
337
    # Local default instance
338
    'local_default': pymssql.connect('.', 'sa', 'pass', 'db'),
339
    
340
    # Local named instance  
341
    'local_named': pymssql.connect('.\\SQLEXPRESS', 'sa', 'pass', 'db'),
342
    
343
    # Remote with port
344
    'remote_port': pymssql.connect('server:1433', 'user', 'pass', 'db'),
345
    
346
    # Remote with comma syntax
347
    'remote_comma': pymssql.connect('server,1433', 'user', 'pass', 'db'),
348
    
349
    # IP address
350
    'ip_address': pymssql.connect('192.168.1.100', 'user', 'pass', 'db'),
351
    
352
    # FQDN
353
    'fqdn': pymssql.connect('sql.company.com', 'user', 'pass', 'db')
354
}
355
```
356

357
### Error Handling and Timeouts
358

359
```python
360
import pymssql
361
from pymssql.exceptions import *
362

363
try:
364
    # Connection with timeouts
365
    conn = pymssql.connect(
366
        server='slow-server',
367
        user='user',
368
        password='password',
369
        database='db',
370
        login_timeout=30,     # 30 second connection timeout
371
        timeout=60           # 60 second query timeout
372
    )
373
    
374
    cursor = conn.cursor()
375
    cursor.execute("SELECT * FROM large_table")
376
    
377
except InterfaceError as e:
378
    print(f"Connection interface error: {e}")
379
except OperationalError as e:
380
    print(f"Connection operational error: {e}")
381
except Exception as e:
382
    print(f"Unexpected error: {e}")
383
```
384

385
## Configuration Best Practices
386

387
### Production Environment
388

389
```python
390
import pymssql
391

392
# Production configuration
393
conn = pymssql.connect(
394
    server='prod-server.company.com',
395
    user='app_user',            # Dedicated application user
396
    password='strong_password',  # Strong password
397
    database='production_db',
398
    
399
    # Security
400
    encryption='require',       # Force SSL
401
    
402
    # Performance  
403
    autocommit=False,          # Explicit transaction control
404
    timeout=30,                # Reasonable query timeout
405
    arraysize=100,             # Balanced fetch size
406
    
407
    # Monitoring
408
    appname='ProductionApp-v1.2.3',
409
    
410
    # Reliability
411
    login_timeout=15,          # Quick connection timeout
412
    conn_properties=[
413
        'SET ARITHABORT ON',
414
        'SET LOCK_TIMEOUT 10000'  # 10 second lock timeout
415
    ]
416
)
417
```
418

419
### Connection Validation
420

421
```python
422
import pymssql
423
from pymssql import _mssql
424

425
def validate_connection(conn):
426
    """Validate that connection is healthy."""
427
    try:
428
        if hasattr(conn, 'connected'):
429
            # Low-level connection
430
            if not conn.connected:
431
                return False
432
            conn.execute_scalar("SELECT 1")
433
        else:
434
            # High-level connection
435
            cursor = conn.cursor()
436
            cursor.execute("SELECT 1")
437
            cursor.fetchone()
438
            cursor.close()
439
        return True
440
    except:
441
        return False
442

443
# Usage
444
conn = pymssql.connect('server', 'user', 'pass', 'db')
445
if not validate_connection(conn):
446
    conn.close()
447
    conn = pymssql.connect('server', 'user', 'pass', 'db')
448
```