SQL Server Connection Diagnostics with sqlcmd

# Add Microsoft repository
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

# For Ubuntu 22.04
curl https://packages.microsoft.com/config/ubuntu/22.04/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list

# For Ubuntu 20.04
curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list

# Install
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y mssql-tools unixodbc-dev

# Add to PATH
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

sqlcmd -S <server_address> -U <username> -P '<password>'

sqlcmd -S 192.168.1.100 -U sa -P 'MyPassword123'

sqlcmd -S tcp:<server_address>,<port> -U <username> -P '<password>'

sqlcmd -S tcp:192.168.1.100,1433 -U sa -P 'MyPassword123'

sqlcmd -S <server_address> -U <username> -P '<password>' -d <database_name>

sqlcmd -S 192.168.1.100 -U sa -P 'MyPassword123' -d MyDatabase

sqlcmd -S <server> -U <user> -P '<password>' -C

sqlcmd -S <server> -U <user> -P '<password>' -l 30

# Test if port is open
telnet <server_address> 1433

# Or use netcat
nc -zv <server_address> 1433

sqlcmd -S <server><instance_name> -U <user> -P '<password>'

sqlcmd -S MYSERVERSQLEXPRESS -U sa -P 'MyPassword123'

sqlcmd -S <server> -E

SELECT @@VERSION;
GO

SELECT name, database_id, create_date 
FROM sys.databases
ORDER BY name;
GO

SELECT DB_NAME() AS CurrentDatabase;
GO

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME;
GO

SELECT 
    SERVERPROPERTY('ProductVersion') AS Version,
    SERVERPROPERTY('ProductLevel') AS ProductLevel,
    SERVERPROPERTY('Edition') AS Edition;
GO

SELECT GETDATE() AS CurrentDateTime;
GO

quit

# Check if port is open
telnet <server_address> 1433
# Press Ctrl+] then type 'quit' to exit

# Alternative with netcat
nc -zv <server_address> 1433

sqlcmd -S tcp:<server>,1433 -U <username> -P '<password>' -l 30

sqlcmd -S tcp:<server>,1433 -U <username> -P '<password>' -C -l 30

sqlcmd -S tcp:<server>,1433 -U <username> -P '<password>' -C -l 30 -d <database_name>

-- Version
SELECT @@VERSION;
GO

-- List databases
SELECT name FROM sys.databases;
GO

-- Switch database
USE MyDatabase;
GO

-- Check tables
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';
GO

quit

# Check if server is accessible
sqlcmd -S tcp:<server>,1433 -U <user> -P '<pass>' -C -Q "SELECT @@VERSION"

# List databases
sqlcmd -S tcp:<server>,1433 -U <user> -P '<pass>' -C -Q "SELECT name FROM sys.databases"

# Count tables in a database
sqlcmd -S tcp:<server>,1433 -U <user> -P '<pass>' -C -d MyDatabase -Q "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES"

# On local machine: Create SSH tunnel
ssh -L 1433:remote-sql-server:1433 user@bastion-host -N -f

# Test connection through tunnel
sqlcmd -S tcp:localhost,1433 -U MyUser -P 'MyPassword' -C -l 30 -d MyDatabase

# Run a quick test
sqlcmd -S tcp:localhost,1433 -U MyUser -P 'MyPassword' -C -Q "SELECT DB_NAME()"