SQL Server Database Utility

This Python utility provides a secure connection pooling mechanism for SQL Server, ensuring encrypted storage of database credentials using RSA public-private key encryption.

Developers using this library only retrieve connections they never handle raw credentials, ensuring security and consistency across applications.

Features

• Secure Credential Storage → Database config is stored in catalogue.dll, encrypted with RSA.
• Connection Pooling → Efficient reuse of database connections to improve performance.
• Developer-Friendly → Developers just import the library and get a connection.
• Asymmetric Encryption (RSA) → No passphrase needed, only a private key for decryption.
• Supports Read & Write Queries → Developers can execute SELECT, INSERT, UPDATE, and DELETE.

project_root/

• generate_keys.py # Generates RSA public-private key pair
• encrypt_catalogue_rsa.py # Encrypts database credentials using RSA encryption into catalogue.dll
• encrypt_catalogue_simple.py # Encrypts database credentials using simple encryption into catalogue.dll
• database_pool.py # Handles connection pooling & decryption
• client.py # Sample script demonstrating usage
• requirements.txt # Dependencies for the project
• README.md # Documentation (this file)

This Python utility allows you to interact with a SQL Server database by running SELECT, INSERT, or other queries directly from the command line.

Step 1: Generate RSA Key Pair Before encrypting credentials, generate RSA keys:

python generate_keys.py

This creates:

• private_key.pem (Keep this secret – used for decryption)
• public_key.pem (Used for encrypting credentials)

Step 2: Encrypt Database Credentials Edit encrypt_catalogue.py and update the database connection string:

config = { "database": { "connection_string": "DRIVER={ODBC Driver 17 for SQL Server};" "SERVER=your_server;" "DATABASE=your_database;" "UID=your_user;" "PWD=your_password;", "pool_size": 5 } } Then encrypt it: python encrypt_catalogue.py

This creates catalogue.dll, which stores encrypted credentials.

Step 3: Use Connection Pool in Your Application Developers only need to import and run queries—no need to manage credentials!

Example: SELECT Query from database_pool import SQLServerDatabase

query = "SELECT * FROM your_table" results = database.read_query(query)

for row in results: print(row)

Example: INSERT Query

query = "INSERT INTO your_table (column1, column2) VALUES (?, ?)" params = ('value1', 'value2')

database.write_query(query, params) print("Data inserted successfully.")

How It Works (Under the Hood)

• Database credentials are encrypted using the RSA public key and stored in catalogue.dll.
• When database_pool.py runs, it decrypts catalogue.dll using the private key.
• A connection pool is created, ensuring efficient reuse of database connections.
• Developers can retrieve connections and run queries without needing to know credentials.

Security Best Practices

• DO NOT share private_key.pem – only the app should have access.
• Store private_key.pem in AWS KMS, Azure Key Vault, or HashiCorp Vault or equivalent for additional security.
• Rotate RSA keys periodically and re-encrypt catalogue.dll.

Installation

Install required dependencies: pip install -r requirements.txt

requirements.txt Includes

pyodbc cryptography

Usage

Read Query (SELECT):

python client.py or python client.py --query "SELECT * FROM your_table" --query_type "read"