1. Copy the code below into a file named mssql.yaml
2. Apply the deployment file to your kubernetes cluster using:
sed -i s/TXlDMG05bCZ4UEBzc3cwcmQ=/$(pwgen -s 120 1 | base64 -w 0)/ mssql.yaml;
kubectl apply -f mssql.yaml
Note: username and password are base64 encoded and NOT encrypted.
Therefore do not store your credentials this way in a production environment, use
read -sep "Enter mssql sa password: " mssql_sa_pass; kubectl create secret generic mssql2 --from-literal=password=$mssql_sa_pass --type=kubernetes.io/basic-auth
instead of adding it to the deployment yaml file.
- name: mssql
- containerPort: 1433
- name: MSSQL_PID
- name: ACCEPT_EULA
- name: SA_PASSWORD
- name: mssql
- name: mssql
- name: install
- name: mssql
- protocol: TCP
Basically if you’re admin you own everything.
So how would you access an SQL-Server if you don’t have SQL-Permissions but you’re administrator on the system the SQL-server is running on?
Basically you can abuse the fact, that the sql-server has to have at least one account with the necessary permissions. Often this is the local system user.
- So just become system using psexec (or anything else): psexec -s cmd.exe
- Search for your local SQL-Instance: osql -L
- Connect to your SQL-Server using your current (system) credentials: osql -S <InstanceName> -E
- If the connection is successful, just enable the sa account and set a new password for it. After that you can use the SQL-Management Studio to administer all permissions:
/* Enable SQL-User authentication and set sa password */
ALTER LOGIN sa ENABLE;
/* Reset the password, requires CONTROL SERVER permission */
ALTER LOGIN sa WITH PASSWORD = '<newPassword>';
/* just set sa password (alternative way)*/
sp_password NULL, '<newPassword>', 'sa';
/* To add an windows user to the admins role do this */
CREATE LOGIN [DOMAIN\USER] FROM WINDOWS;
EXEC sp_addsrvrolemember 'DOMAIN\USER', 'sysadmin';
If the above is throwing an access denied error for you try a different user for example the mssql service user use process hacker to run a cmd prompt in the context of that user and try again. If also that fails, check if there is any application accessing the database that could have an account with too much permissions e. g. Server Admin instead of db_owner and try that one.
- If that all does not help, you have one last way of getting into the database, but that causes a downtime.
- Stop your SQL-Server Services (Server, Browser, Agent, …) and open an elevated cmd.
- Navigate to the folder containing the executable of the service and start it with the parameter “-m” for single user mode without authentication. After the server is back up do Step 4 and after that stop the server and restart the services you stopped earlier.
WARNING: BY DOING STEP 7 EVERYBODY COULD GAIN FULL ACCESS TO YOUR SERVER, DEPENDING ON YOUR INFRASTRUCTURE YOU MAY WANT TO BLOCK REMOTE CONNECTIONS ON THE WINDOWS FIREWALL WHILE PERFORMING THIS STEP
To be able to use the “sqlps” PowerShell Module you first need to install it from: Link
- SQLSysClrTypes.msi – CLR Types for SQL Server
- SharedManagementObjects.msi – Shared Management Objects
- PowerShellTools.msi – PowerShell Extension for SQL Server
For information on how using this cmdlets, look it up in the ISE or here.
Using .net api:
# SQL-Server settings
$Database = "Database" # Database name
$Server = "SERVER\SQLEXPRESS"; # SQL-Server Instanz
# Connect to SQL and query data, extract data to SQL Adapter
$SqlQuery = "SELECT [Report],[Filiale],[E-Mail] FROM [dbo].[verteiler]"; # The query
## Example Database Layout
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$nRecs = $SqlAdapter.Fill($DataSet)
$nRecs | Out-Null
$objTable = $DataSet.Tables