【SQLServer】Microsoft SQL Baseline Checklist

今天调查了Microsoft SQL Baseline Checklist中的下面几个问题。

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

 1.Hide Instances 

原文：https://msdn.microsoft.com/en-us/library/ms179327.aspx

注意：隐藏数据库实例后，需要在ConnectionString里追加端口号。

● 如何配置数据库监听特定端口？

原文：https://msdn.microsoft.com/en-us/library/ms177440.aspx

● ConnectionString中设定端口号方法：

参考：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

 use [master]

 EXEC sp_configure 'show advanced options', 1

 GO

 RECONFIGURE

 GO

 SELECT * FROM SYS.configurations WHERE name = 'show advanced options'

 -- Disabling xp_cmdshell

 EXEC sp_configure 'xp_cmdshell',0

 GO

 RECONFIGURE

 GO

 -- Check the Disabled record.

 SELECT * FROM sys.configurations WHERE name = 'xp_cmdshell'

 -- Enabling xp_cmdshell

 EXEC sp_configure 'xp_cmdshell',1

 GO

 RECONFIGURE

 GO

 -- Check the Enabled record.

 SELECT * FROM sys.configurations WHERE name = 'xp_cmdshell'

 EXEC sp_configure 'show advanced options', 0

 GO

 SELECT * FROM SYS.configurations WHERE name = 'show advanced options'

原文：http://www.c-sharpcorner.com/Blogs/9579/enabling-disabling-xp_cmdshell-in-sql-server.aspx

注意：最好不要修改既存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

原文：https://msdn.microsoft.com/en-us/library/ms177285.aspx

设定：

USE [master]

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 12

GO

还原：

USE [master]

GO

EXEC xp_instance_regdeletevalue N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs'

GO

4.Remote Access

参考：http://blogs.msdn.com/b/walzenbach/archive/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008.aspx

设定：

EXEC sys.sp_configure N'remote access', N''

GO

RECONFIGURE WITH OVERRIDE

GO

还原：

EXEC sys.sp_configure N'remote access', N''

GO

RECONFIGURE WITH OVERRIDE

GO

附1.命令行调用SQL文件（SqlServer）

set user="userid"

set password="password"

set server="sbserver\instance"

osql -U %user% -P %password% -S %server% -i "Setup\SqlFileName.sql" >> logfile.log