Mircosoft SQL Server cheat sheet

Since I have not worked much with SQL Server, its always a challenge for me to remember how to roles, permissions and do a quick backup, etc.
I don't run into it everyday so I created this cheat sheet to help me remember how things are done the SQL Server way.

Logins / users / roles SQL Server permissions:

-- List principals defined on this database:
use jira881;
go
select * from sys.database_principals

-- List all the grants given to certain principal
use jira850;
go
SELECT pr.principal_id, pr.name as pinciple_name, pr.type_desc,
   pr.authentication_type_desc, pe.state_desc, pe.permission_name
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
   ON pe.grantee_principal_id = pr.principal_id;

-- List all users who own a database on the server
select suser_sname(owner_sid) as 'Owner', state_desc, *
from master.sys.databases

-- List all users defined server wide
select * from master.sys.server_principals

-- List all Server wide logins available and the login type:
select sp.name as login,
   sp.type_desc as login_type,
   sl.password_hash,
   sp.create_date,
   sp.modify_date,
   case when sp.is_disabled = 1 then 'Disabled'
     else 'Enabled' end as status
from sys.server_principals sp
left join sys.sql_logins sl
     on sp.principal_id = sl.principal_id
where sp.type not in ('G', 'R')
order by sp.name;

-- list users in db_owner role for a certain database:
USE MyOptionsTest;
GO
SELECT members.name as 'members_name', roles.name as 'roles_name',roles.type_desc as 'roles_desc',members.type_desc as 'members_desc'
FROM sys.database_role_members rolemem
INNER JOIN sys.database_principals roles
ON rolemem.role_principal_id = roles.principal_id
INNER JOIN sys.database_principals members
ON rolemem.member_principal_id = members.principal_id
where roles.name = 'db_owner'
ORDER BY members.name

-- List SQL server dbrole vs database user mapping
USE MyOptionsTest;
GO
SELECT DP1.name AS DatabaseRoleName,
   isnull (DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT OUTER JOIN sys.database_principals AS DP1
   ON DRM.role_principal_id = DP1.principal_id
LEFT OUTER JOIN sys.database_principals AS DP2
   ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = 'R'
ORDER BY DP1.name;

-- Create a server wide login
CREATE login koki
WITH password = 'Koki_123_123'
go

-- create a user mapped to a server wide login:
USE MyOptionsTest;
GO
CREATE USER koki_mot FOR LOGIN koki
go
USE MyOptionsTest;
GO
EXEC sp_addrolemember 'db_owner', 'koki_mot';
go

USE jira881;
GO
CREATE USER koki_881 FOR LOGIN koki;
go
EXEC sp_addrolemember 'db_owner', 'koki_881';
go

Collations:

use master;
go
create database testdb1
collate Latin1_General_CI_AS
go

use master;
go
create database testdb_bin
collate SQL_Latin1_General_CP850_BIN2
go

-- list all the binary collations available on the server:
SELECT Name, Description FROM fn_helpcollations() WHERE Name like '%bin2%'

Backup:

-- take a database backup
BACKUP DATABASE MyOptionsTest
   TO DISK = '/tmp/MyOptionsTest.bak'
     WITH FORMAT;
GO

-- restore a the backup to a different db
RESTORE DATABASE MOTest
   FROM DISK = '/tmp/MyOptionsTest.bak'
   WITH
     MOVE 'MyOptionsTest' TO '/var/opt/mssql/data/MOTest.mdf',
     MOVE 'MyOptionsTest_log' TO '/var/opt/mssql/data/MOTest_log.ldf'

use MyOptionsTest
go
delete from dbo.t1
select * from dbo.t1

-- restore backup to same database
use master;
go
RESTORE DATABASE MyOptionsTest
   FROM DISK = '/tmp/MyOptionsTest.bak'
   WITH REPLACE
GO

mssql-scripter:

2023 pip install mssql-scripter
2024 mssql-scripter -S localhost -d jira850 -U koki
2028 mssql-scripter -S localhost -d jira881 -U koki --schema-and-data > ./jira881_mssql_scripter_out.sql
2029 ls -ltr
2030 vim jira881_mssql_scripter_out.sql
2031 mssql-scripter -S localhost -d jira881 -U koki --schema-and-data > ./jira881_mssql_scripter_out.sql

mssql-scripter is exceptionally useful, it allowed me to export the whole database in text format, which enables me to do text manipulations using Unix filters on the data, also allows the use of tools like diff and GUI diffuse to compare exports done after certain application operations.
Very useful tool.

Configure Apache to increase protection against XSS and ClickJacking attacks.

Many software companies now use automated security test tools and many of them do a regular set of scans to ensure their application is well protected and secure.

One of the good tips to help minimized the reported issues and to avoid false positives, is to configure the application reverse proxy in the right way.
In this post, I will focus on Apache and how to add a simple configuration to mitigate multiple reported issues found by automated tools.

In this example, I am scanning a simple Java web form running behind Apache which acts as a reverse proxy in this case.
Below is the initial scan report:

 

As you can see, there are multiple findings in the scan report, mainly we have problems with the below:

• X-Frame-Options Header Not Set
• X-Content-Type-Options Header Missing
• Absence of Anti-CSRF Tokens
• Cookie Without SameSite Attribute
• Charset Mismatch (Header Versus Meta Charset)  (informational finding)

We can mitigate most of those issues by properly setting headers in the Apache configuration, this should avoid most of the above issues.

Below is an example configuration for the domain http://172.17.0.1/:

# Set those headers to enable right CORS headers.
Header always set Access-Control-Allow-Origin "http://172.17.0.1"
Header always set Access-Control-Allow-Methods "POST, GET, OPTIONS, DELETE, PUT"
Header always set Access-Control-Max-Age "1000"
Header always set Access-Control-Allow-Headers "x-requested-with, Content-Type, origin, authorization, accept, client-security-token"
Header always set Access-Control-Expose-Headers "*"

# Added a rewrite to respond with a 200 SUCCESS on every OPTIONS request.
RewriteEngine On
RewriteCond %{REQUEST_METHOD} OPTIONS
RewriteRule ^(.*)$ $1 [R=200,L]

# Set x-frame-options to sameorigin to combat click-jacking.
Header always set X-Frame-Options  SAMEORIGIN

# Set Samesite to strict to counter potential session cookie hijacking, also helps protect 
# from CSRF though session cookie.
Header edit Set-Cookie ^(.*)$ $1;SameSite=strict

# Set X-Content-Type-Options to nosniff to avoid MIME sniffing.
Header always set X-Content-Type-Options nosniff

As you can see, the above rules address multiple issues reported in the scan, mainly to protect agains CORS problems,click-jacking, XSS though cookies and Anti-MIME sniffing.

Once that configuration is in effect, the number of reported issues is much less:

The last problem related to CSRF is an architectural problem in the application.
Modern web applications are encouraged to use tokens injected into the HTML pages and forms, dynamic generated content and on server side to validate that same user with same session is one who is submitting the request without being hijacked by some malicious attack.
Check https://en.wikipedia.org/wiki/Cross-site_request_forgery for more information.

Modsecurity does have rules that work by injecting content to help with CSRF, though, if applied without change, it could break the application functionality.