Cross db ownership chaining and security holes

I was doing a routine security audit on a pre production SQL Server and noticed cross db ownershjp turned on for the instance.  The application was designed with a cross db ownership requirement.

My general approach to cross db ownership and security in general , is to grant only what is necessary. Systems evolve, configurations change, security hole appear. This security approach , often comes into conflict with business and programming requirements , who want everything now!

Questions I ask the owners about Cross db ownership:

1)       Is SQL Server Instance cross db ownership required at the instance level or database level?

2)       Will all the databases in the instance participate in the cross db ownership? If not , then change cross db ownership to the database level.

3)       Can the owner produce a document , detailing the application requirement fro cross db ownership? Rather than blindly granting cross db ownership on all the databases in an instance- have the application owner detail the requirement.

4)       Are you completing regular SQL Server  security audits?

 

--queries to check if cross db ownership is on
SELECT value_in_use FROM sys.configurations 
WHERE name='cross db ownership chaining' 


EXEC sp_configure 'Cross DB Ownership Chaining'

 Read More

 

How to create a SQL Server Security Audit - SQL Server DBA

Powershell sql server security audit - SQL Server DBA

Find who made a database security change - SQL Server DBA

 

Off the shelf software and installation prerequisites

Companies purchase off the shelf  software to cover all aspects of the business. I’m including: Payroll, Web Filtering, Security audits,Monitoring, Bookeeping, modelling .. the list is endless. Many of these products are dependant on databases.  

The typical situation is a list of instructions on how to make environment changes, security changes , DDL to create the database with objects.

The Installation Prerequiste needs a special mention. The vendor installation guide  has information relevant to the database creation. I’m amazed by some of the recommendations. Some examples recently:

1)       Autogrowth of data and log files at 50% . Read more on SQL LOG FILE AUTOGROW performance troubleshooting

2)       DBCC SHRINKDATABASE every night

3)       Logon mapped to database user is set with sysadmin privileges

 None of the 3 examples  fit into any  best practises . At this point I contact the vendor , explain the situation and they normally agree but with a caveat “they’ll take no responsibility if there is a product issue”.

Why does this situation arise?

 Most vendors release products on certain assumptions

1)       There is no expert DBA or IT support in the customer company

2)       Expectation of system to run unattended

3)       No knowledge of customer network

4)       No knowledge of customer hardware

A quality DBA will identify problems ,  often associated with background tasks or maintenance procedures. They will phone the vendor , who will recommend some adjustments

It should be a minimum requirement for all vendors to supply a detailed FAQ for DBAs to manage the system. The in-house DBA is likely to have a standardised approach customised to the system.  Integrating off the shelf software causes unnecessary overhead if every recommendation is followed

In some cases the recommendations are justified and explained in full, but that’s an exception , not the standard level of documentation.

Read More

SQL Server Performance Killers - SQL Server DBA

Write everything down during problem ... - SQL Server DBA

SQL Server – DBA tactics

SQL Server – Find SQL Server tcp port with Powershell

I developed some scripts to gather information for a Disaster Recovery preparation.  Finding and storing the port number used by a SQL Server Instance was a requirement. The SQL Servers are 2012 , which allowed the sys.dm_server_registry DMV  to be used.  To find earlier versions TCP port information , it is necessary to investigate the Windows registry keys

 The sys.dm_server_registry returns SQL Server Instance specific Windows registry details. One of the rows is “TcpPort”.

The Powershell Invoke-sqlcmd cmdlet is useful when using T-SQL or XQuery and  sqlcmd is the preferred method of connecting to the SQL Server.

 

Invoke-sqlcmd -ServerInstance "MyServer\MyInstance" -Query "select value_data AS Port from sys.dm_server_registry WHERE value_name = 'TcpPort'"

 Related Posts

TCP Chimney offload - SQL Server DBA

Error 26073 and TCP connection closed - SQL Server DBA

Powershell and Disaster Recovery preparation – T-SQL

How to decide on Index - ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS

Question: What does the ALLOW_ROW_LOCKS  and ALLOW_PAGE_LOCKS mean on the CREATE INDEX statement ? What is the cost\benefit of  ON|OFF?   Is there a performance gain?

USE [DB]
GO
CREATE NONCLUSTERED INDEX [ui_1_temp_nc] ON [dbo].[myTable] 
(
	[col1] ASC,
	[col2] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

 

Answer:

 1)   SQL Server  takes locks at different levels – such as table, extent, page, row. ALLOW_PAGE_LOCKS and ALLOW_ROW_LOCKS decide on whether ROW or PAGE locks are taken.

2)       If ALLOW_PAGE_LOCKS = OFF, the lock manager will not take page locks on that index. The manager will only user row or table locks

3)       If ALLOW_ROW_LOCKS = OFF , the lock manager  will not take row locks on that index. The manager will only use page or table locks.

4)       If ALLOW_PAGE_LOCKS = OFF  and ALLOW_PAGE_LOCKS = OFF , locks are assigned at a table level only

5)       If  ALLOW_PAGE_LOCKS = ON  and ALLOW_PAGE_LOCKS = ON , SQL decides on which lock level to create according to the amount of rows and memory available.

6)       Consider these factors , when deciding to change the settings. There has to be an extremely good reason , backed up by some solid testing before you can justify changing to OFF

ALTER TRACE and developers

Question: SQL Server Profile Trace ,during debugging and developing new applications  is invaluable – I currently have a bug I’m analysing where I need to know exactly what SQL statement is executed but without the SQL profile analyser trace functionality I’m at a bit of a loss unless you know of a better way?

Is there any reason I couldn’t ask to have ALTER TRACE permissions on the development instances of SQL?

Answer: Requesting SQL Server trace functionality is common. As a DBA – I ask developers to make the request – specifying the duration they require the ALTER TRACE privilege.  In older SQL server versions – sysadmin authority was required – which presented problems with SQL Server Security Policies.

 The ALTER TRACE syntax is :

GRANT ALTER TRACE TO hendrix