The Debugging Gist

SQL Server Installation - Production Checklist

Sep 4, 2025

Change default SA password to complex password

Disable SA account and create dedicated admin accounts

//Login via sa
sqlcmd -S localhost,1433 -U sa

-- Create a dedicated SQL admin account
CREATE LOGIN [sql_admin] WITH PASSWORD = 'STRONG_PASSWORD_HERE';
ALTER SERVER ROLE sysadmin ADD MEMBER [sql_admin];

-- Create an application admin account (less privileged)
CREATE LOGIN [app_admin] WITH PASSWORD = 'STRONG_PASSWORD_HERE';
ALTER SERVER ROLE dbcreator ADD MEMBER [app_admin];
ALTER SERVER ROLE securityadmin ADD MEMBER [app_admin];

go

Verify the accounts were created

-- Verify the accounts were created
SELECT 
    sp.name as login_name,
    sp.is_disabled,
    sr.name as server_role
FROM sys.server_principals sp
LEFT JOIN sys.server_role_members srm ON sp.principal_id = srm.member_principal_id
LEFT JOIN sys.server_principals sr ON srm.role_principal_id = sr.principal_id
WHERE sp.type = 'S' AND sp.name IN ('sql_admin', 'app_admin', 'sa')
ORDER BY sp.name;

Test New Admin Account. Before disabling SA, test the new admin account:

 Exit current session
EXIT

# Test new admin account
sqlcmd -S localhost -U sql_admin

-- Test administrative capabilities
SELECT @@VERSION;
SELECT SUSER_NAME();
SELECT IS_SRVROLEMEMBER('sysadmin');
GO
EXIT

Disable the SA Account

Once you've confirmed the new admin account works:

sqlcmd -S localhost,14330 -U sql_admin

-- Disable the SA account
ALTER LOGIN sa DISABLE;

-- Verify SA is disabled
SELECT name, is_disabled FROM sys.server_principals WHERE name = 'sa';
GO

Change Default Port (Default 1433)

# Stop SQL Server
sudo systemctl stop mssql-server
# Set the new port (example: 2568)
sudo /opt/mssql/bin/mssql-conf set network.tcpport 2568
# Start SQL Server
sudo systemctl start mssql-server

# Verify the change
sudo systemctl status mssql-server

Verification Commands

# Check what ports SQL Server is listening on
sudo netstat -tulpn | grep sqlservr

# Verify configuration syntax
sudo /opt/mssql/bin/mssql-conf validate

# Test Connection
sqlcmd -S localhost,2568 -U sa

If connection fails and SELinux is enabled, we may have to enable the new port

# Allow the new port for SQL Server
sudo semanage port -a -t mssql_port_t -p tcp 2568

Configure Firewall (If Needed)

sudo firewall-cmd --permanent --add-port=2568/tcp
sudo firewall-cmd --reload

# Test port connectivity from client machine
telnet your_server_ip 1433

Set memory limits based on available RAM

# Memory configuration (example: 16GB system, allocate 12GB to SQL)
sudo /opt/mssql/bin/mssql-conf set memory.memorylimitmb 12288

# Enable trace flags for optimal performance
sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true
sudo /opt/mssql/bin/mssql-conf set telemetry.customerfeedback false

sudo systemctl restart mssql-server

Monitor and Audit Login Activity

- Enable login auditing (requires server restart)
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'login auditing', 2; -- Both successful and failed logins
RECONFIGURE;
GO

-- Create a procedure to check recent logins
CREATE PROCEDURE CheckRecentLogins
AS
BEGIN
    SELECT 
        session_id,
        login_name,
        host_name,
        program_name,
        login_time,
        last_request_start_time
    FROM sys.dm_exec_sessions
    WHERE is_user_process = 1
    ORDER BY login_time DESC;
END;
GO

Samyeak Maharjan