"SQL Server" Microsoft SQL Baseline Checklist

Today investigates the following issues in Microsoft SQL Baseline Checklist.

1. Hide Instances
2. Extended Store Procedures
3. Maximum number of Error Log Files
4. Remote Access

1.Hide Instances

2. In SQL Server Configuration Manager , expand SQL Server Network configuration, right-click <server instance> Protocol, and then select "Properties".

4. On the Flags tab, in the hidden instance box, select Yes, and then click OK to close the dialog box. for new connections, the changes take effect immediately.

Original: Https://msdn.microsoft.com/en-us/library/ms179327.aspx

Note: After you hide the DB instance, you need to append the port number to the ConnectionString.

How do I configure a database to listen on a specific port?

2. In the console pane of SQL Server Configuration Manager, expand SQL Server Network configuration, click< instance name > Protocols, and then double-click TCP/IP.

3. In"TCP/IP Properties"of the dialog box"IP Address"tab, several IP addresses are displayed in the format:IP1、IP2..., untilIPAll。 One of these IP addresses is the IP address of the loopback adapter (127.0.0.1). Right-click each address, and then click Properties to identify the IP address that you want to configure.

5. If the TCP dynamic Port dialog box contains 0, it indicates that the database engine is listening on dynamic ports, delete 0.

7. In the IPn properties Area box, in the TCP port box, type the port number that you want this IP address to listen on, and then click OK.

9. In the console pane, click SQL Server Service.

11. In the details pane, right-click SQL Server (< instance name >), and then click Restart to stop and restart SQL Server.

Original: Https://msdn.microsoft.com/en-us/library/ms177440.aspx

Set the port number method in ConnectionString:

mycomputer.test.xxx.com,1234

Reference: Http://stackoverflow.com/questions/5294721/how-to-specify-a-port-number-in-sql-server-connection-string

2.Extended Store Procedures

Disable and Enable Extended Store procedures

1  Use [Master]2 3 EXECsp_configure'Show advanced Options',14 GO5 RECONFIGURE6 GO7 8 SELECT *  fromSys.configurationsWHEREName= 'Show advanced Options'9 Ten --disabling xp_cmdshell One EXECsp_configure'xp_cmdshell',0 A GO - RECONFIGURE - GO the  - --Check the Disabled record. - SELECT *  fromSys.configurationsWHEREName= 'xp_cmdshell' -  + --enabling xp_cmdshell - EXECsp_configure'xp_cmdshell',1 + GO A RECONFIGURE at GO -  - --Check the Enabled record. - SELECT *  fromSys.configurationsWHEREName= 'xp_cmdshell' -  - EXECsp_configure'Show advanced Options',0 in GO -  to SELECT *  fromSys.configurationsWHEREName= 'Show advanced Options'

Original: Http://www.c-sharpcorner.com/Blogs/9579/enabling-disabling-xp_cmdshell-in-sql-server.aspx

Note: It is best not to modify the settings of the existing SP.

https://social.msdn.microsoft.com/forums/sqlserver/en-US/17c7569d-85c0-40ca-b921-cd58b31af612/ Disabling-extended-stored-procedures

Revoked from public

 Use [master] GO REVOKE EXECUTE  on [sys]. [xp_dirtree]  to [public] GO

Grant to Public

 Use [master] GO GRANT EXECUTE  on [sys]. [xp_dirtree]  to [public] GO

3.Maximum number of Error Log Files

2. In Object Explorer, expand the instance of SQL Server, expand Administration , right-click SQL Server logs , and then click Configure .

3. In the configure SQL Server Error Log dialog box, select from the following options.

   Limit the number of error log files before recycling

   Select this option to limit the number of error logs that can be created before the error log is reclaimed. A new error log is created each time the instance of SQL Server is started. SQL Server retains a backup of the first six logs, unless this option is selected and a different maximum number of error log files is specified below.

   Maximum number of error log files

   Specifies the maximum number of error log files created before the error log file is reclaimed. The default value is 6, which is the number of previous backup logs that SQL Server retains before it recycles the backup log.

Original: Https://msdn.microsoft.com/en-us/library/ms177285.aspx

4.Remote Access

1. In the Object Explorer, right-click the database and tapProperties.

2. Click on the "Connections" tab.

3. Remove the "Allowremote connectionsto this server" tick.

Reference: Http://blogs.msdn.com/b/walzenbach/archive/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008.aspx

"SQL Server" Microsoft SQL Baseline Checklist