Posts Tagged ‘SP_Configure’

SQL Server HARDENING best practices after Installation

Posted: February 6, 2015 by Virendra Yaduvanshi in Database Administrator
Tags: , , , , , , , ,
1

Hi Guys, Now days Payment Card Industry Data Security Standards (PCI DSS) is a preventative standard intended to reduce the risk of payment card fraud and information theft. As per my views after SQL server installation on Production Box, I hope below settings should be set using SP_CONFIGURE as,

  • The ‘Ad Hoc Distributed Queries’ Server Configuration Option should be set to ‘0’
  • The ‘CLR Enabled’ Server Configuration Option should be set to ‘0’
  • The ‘Cross DB Ownership Chaining’ Server Configuration Option should be set to ‘0’
  • The ‘Database Mail XPs’ Server Configuration Option should be set to ‘0’.
  • The ‘Ole Automation Procedures’ Server Configuration Option should be set to ‘0’
  • The ‘Remote Access’ Server Configuration Option should be set to ‘0’
  • The ‘Remote Admin Connections’ Server Configuration Option should be set to ‘0’.
  • The ‘Scan For Startup Procs’ Server Configuration Option should be set to ‘0’.
  • The ‘SQL Mail XPs’ Server Configuration Option should be set to ‘0’
  • The ‘Trustworthy’ Database Property should be set to Off
  • Unnecessary SQL Server Protocols should be disabled.
  • SQL Server should be configured to use non-standard ports
  • The ‘Hide Instance’ option should be set to ‘Yes’ for production SQL Server instances.
  • Non-clustered SQL Server instances within production environments should be

    designated as hidden to prevent advertisement by the SQL Server Browser service.

  • The ‘sa’ login account should be disabled or renamed.

Extended Stored Procedures

The following extended stored procedures should not be used by any application or maintenance script.

  • Execute on ‘xp_availablemedia’ to PUBLIC should be revoked.
  • The ‘xp_cmdshell’ option should be set to disabled
  • Execute on ‘xp_dirtree’ to PUBLIC should be revoked.
  • Execute on ‘xp_enumgroups’ to PUBLIC should be revoked.
  • Execute on ‘xp_fixeddrives’ to PUBLIC should be revoked.
  • Execute on ‘xp_servicecontrol’ to PUBLIC should be revoked.
  • Execute on ‘xp_subdirs’ set to PUBLIC should be revoked.
  • Execute on ‘xp_regaddmultistring’ to PUBLIC should be revoked.
  • Execute on ‘xp_regdeletekey’ to PUBLIC should be revoked.
  • Execute on ‘xp_regdeletevalue’ to PUBLIC should be revoked
  • Execute on ‘xp_regenumvalues’ to PUBLIC should be revoked.
  • Execute on ‘xp_regremovemultistring’ to PUBLIC should be revoked
  • Execute on ‘xp_regwrite’ to PUBLIC should be revoked
  • Execute on ‘xp_regread’ to PUBLIC should be revoked.

Authentication and Authorization

  • The ‘Server Authentication’ Property should be set to Windows Authentication mode
  • CONNECT permissions on the ‘guest user’ should be revoked within all SQL Server databases excluding the master, msdb and tempdb

  • Orphaned Users should be dropped from SQL Server database. A database user for which the corresponding SQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance and is referred to as orphaned and should be removed.

Password Policies

  • The ‘MUST_CHANGE’ Option should be set to ‘ON’ for all SQL authenticated logins
  • The ‘CHECK_EXPIRATION’ Option should be set to ‘ON’ for all SQL authenticated logins within the ‘Sysadmin’ Role

  • The ‘CHECK_POLICY’ Option should be set to ‘ON’ for all SQL authenticated logins

  • Auditing and Logging
  • The ‘Maximum number of error log files’ setting should be set to greater than or equal to 12.
  • The ‘Default Trace Enabled’ Server Configuration option should be set to 1.
  • ‘Login Auditing’ to both failed and successful logins

 

Thanks for Reading, Keep smiling, keep learning

Execute a Stored Procedure when SQL Server Instance starts

Posted: April 3, 2014 by Virendra Yaduvanshi in Database Administrator
Tags: , , , , , , , ,
0

As a DBA it may be a challenging task to gather some system level or at business level some specific or few data needs to be check at SQL Server startup level. SQL Server provides this feature where we can execute stored procedure when SQL server instance starts and as per requirement we can analyze the data. For same first check system configuration, the option ‘scan for startup procs’ should be set 1.
Steps are as below,

— Step 1 – Lets check Where ‘show advanced options’ option is configured or not, if not, first configure it as

sp_configure ‘show advanced options’ ,1
Reconfigure with override
Go 

— Step 2 – Configure ‘scan for startup procs’ option as

sp_configure ‘scan for startup procs’ ,1

Reconfigure with override
Go

From above syntax, server is now enabled to execute stored procedure at instance startup time.

As we know, when SQL Server starts, it first scans the registry to check the startup parameter values, during this it’s find the master database files and make the master database online. Now, Master Database is online, and startup procedure should be in Master Database and that SP will be execute from Master DB only. (Here is a restriction – Our SP should be in Master DB).

The sp_procoption stored procedure is used to execute the SP when SQL Server service starts as,

EXEC sp_procoption ‘procedure name’, ‘startup’, ‘true’

We can turn-off the Stored procedure execution at SQL server instance startup using below query.

EXEC sp_procoption ‘procedure name’, ‘startup’, ‘false’

Please comment on this topic.

Discontinued Database Engine Functionality in SQL Server 2012

Posted: December 23, 2012 by Virendra Yaduvanshi in Database Administrator
Tags: , , , , , , , , ,
0

Here are Database Engine features that are no longer available in SQL Server 2012.

Category

Discontinued feature

Replacement
Backup and Restore

BACKUP { DATABASE | LOG } WITH PASSWORD and BACKUP { DATABASE | LOG } WITH MEDIAPASSWORD are discontinued. RESTORE { DATABASE | LOG } WITH [MEDIA]PASSWORD continues to be deprecated.

None
Backup and Restore

RESTORE { DATABASE | LOG } … WITH DBO_ONLY

RESTORE { DATABASE | LOG } … … WITH RESTRICTED_USER
Compatibility level

80 compatibility levels

Databases must be set to at least compatibility level 90.
Configuration Options

sp_configure ‘user instance timeout’ and ‘user instances enabled’

Use the Local Database feature. For more information, see SqlLocalDB Utility
Connection protocols

Support for the VIA protocol is discontinued.

Use TCP instead.
Database objects

WITH APPEND clause on triggers

Re-create the whole trigger.
Database options

sp_dboption

ALTER DATABASE
Mail

SQL Mail

Use Database Mail. For more information, see Database Mail and Use Database Mail Instead of SQL Mail.
Memory Management

32-bit Address Windowing Extensions (AWE) and 32-bit Hot Add memory support.

Use a 64-bit operating system.
Metadata

DATABASEPROPERTY

DATABASEPROPERTYEX
Programmability

SQL Server Distributed Management Objects (SQL-DMO)

SQL Server Management Objects (SMO)
Query hints

FASTFIRSTROW hint

OPTION (FAST n).
Remote servers

The ability for users to create new remote servers by using sp_addserver is discontinued. sp_addserver with the ‘local’ option remains available. Remote servers preserved during upgrade or created by replication can be used.

Replace remote servers by using linked servers.
Security

sp_dropalias

Replace aliases with a combination of user accounts and database roles. Use sp_dropalias to remove aliases in upgraded databases.
Security

The version parameter of PWDCOMPARE representing a value from a login earlier than SQL Server 2000 is discontinued.

None
Service Broker programmability in SMO

The Microsoft.SqlServer.Management.Smo.Broker.BrokerPriority class no longer implements theMicrosoft.SqlServer.Management.Smo.IObjectPermission interface.
SET options

SET DISABLE_DEF_CNST_CHK

None.
System tables

sys.database_principal_aliases

Use roles instead of aliases.
Transact-SQL

RAISERROR in the format RAISERROR integer ‘string’ is discontinued.

Rewrite the statement using the current RAISERROR(…) syntax.
Transact-SQL syntax

COMPUTE / COMPUTE BY

Use ROLLUP
Transact-SQL syntax

Use of *= and =*

Use ANSI join syntax. For more information, see FROM (Transact-SQL).
XEvents

databases_data_file_size_changed, databases_log_file_size_changed

eventdatabases_log_file_used_size_changed

locks_lock_timeouts_greater_than_0

locks_lock_timeouts

Replaced by database_file_size_change event, database_file_size_change

database_file_size_change event

lock_timeout_greater_than_0

lock_timeout