访问SQL Server的.net配置文件中禁用的密码字符

'
"
&
<
这四个字符在配置为SQL访问用户密码时会报错.

GFI ReportCenter + EventeManager8生成sql server logon 报告的配置方法

GFI ReportCenter 3.5是GFI系列软件生成报告的工具，最近遇到了一个需求，关于统计SQL用户登录情况的审计，EventeManager（EM）只能提供windows用户登录信息的报告，利用自定义报告的功能实现：

首先要获得报告的数据源，这里不赘述ReportCenter 配置数据源的方法，而是针对本报告的SQL 登录信息的采集：
在SQL Server的实例属性设置登录审计功能，可以对登录信息不记录、记录失败的、记录成功的、都记录，此处设置将影响后面报告能反映的信息，因为ReportCenter 3.5尚不支持更细致的条件过滤，因此，这里设置成哪种审计，报告将如实反映。设置该功能后需要重起sql 实例才能生效。

在自定义报告的节点处随便选择一种报告类型，指定应用程序事件日志（application event），
Events设置手工添加－－
Event ID： 17055，
Source：MSSQLSERVER，
Category ：Logon

Data Filter－－
Rules = SQL Server information (与EM里Rule sets没有对应关系)
and user not include "NT AUTHORITY\SYSTEM"

指定 Report name保存即可。

配置完成，只要选择该报告，运行即可。

后面可以配置定期报告，这样就实现了定期收到SQL审计报告的监控需求。

修正GFI EventsManager8 在设置Failed SQL server logons时的错误

GFI EventsManager8 的Events Browse里关于Application Events\SQL Server events\Failed SQL server logons
的过滤条件设置有错误，导致即使有符合条件的日志也无法显示。

使用SQL监控发现拼接查询的时候使用了错误的字段，本来应该是表的APPLICATION_EVENTS的EXT_TAGS 字段，而默认使用的是 REL_EXT_TAGS ，修改方法如下：

Field 1 匹配条件 “Equal to” 改成 “contains” 即可

通过这个修改，Failed SQL server logons即可正确显示失败的日志。

SQLServer 2005 Agent无法启动的问题

SQLServer 2005服务的登录身份默认是Local System（本地系统帐户）。

如果修改成自己的一个Windows帐户后，Agent就启动不起来了，在Management studio的Agent服务处显示
“禁用代理XP” 在事件里面出现错误：

SQLServerAgent could not be started (reason: SQLServerAgent must be able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the SysAdmin role).

开始以为是这个windows账户必须加到SQL的sa组里，但是添加进去还是不行。

msdn里给的解决方案是“代理 XP”选项

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO

执行脚本时提示：
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Msg 5845, Level 16, State 1, Line 1
Address Windowing Extensions (AWE) requires the 'lock pages in memory' privilege which is not currently present in the access token of the process.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'Agent XPs' does not exist, or it may be an advanced option.
Msg 5845, Level 16, State 1, Line 1
Address Windowing Extensions (AWE) requires the 'lock pages in memory' privilege which is not currently present in the access token of the process.

恰好最近刚刚更换过Cluster服务账户，知道这个'lock pages in memory' 是组策略里的一个设置，修改之后重起SQL服务，然后Agent服务即可启动，修改方法如下：
To enable the lock pages in memory option

On the Start menu, click Run; in the Open box, type gpedit.msc.

The Group Policy dialog box opens.

On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.

Expand Security Settings, and then expand Local Policies.

Select the User Rights Assignment folder.

The policies will be displayed in the details pane.

客户机配置WSUS

客户机配置WSUS：

打开gpedit.msc，配置组策略：
Computer Configuration\Administrative Teplates\Windows Components\Windows update:

Specify intranet Microsoft update service location

enable
http://wsusServerIP

--apply

cmd:
--强制刷新组策略并生效
gpupdate /force
--检查是否有最新的升级包
wuauclt /detectnow

查看有无新的补丁下载了
\windows\windowsUpdate.log


实在不行就重起服务器

Windows2003 Cluster更换启动账户

如何手动重新创建群集服务帐户

Windows Server 2003
注意：如果更改用于启动群集服务的帐户，则必须使用 Windows Server 2003 的“计算机管理”在群集中的每个节点上更改帐户信息。为此，请按照下列步骤操作：

启动 Windows Server 2003 的“计算机管理”，展开“服务和应用程序”分支，然后单击“服务”分支。
在右窗格中，双击群集服务。选择登录选项卡，然后更新帐户信息。

要使群集服务帐户能够在 Microsoft Windows Server 2003 中正常运行，该帐户应显式要求在群集中的所有节点上具有下列权限：

作为操作系统的一部分
调整进程内存配额
备份文件和目录
增加调度优先级
作为服务登录
还原文件和目录

而且，还要确保本地管理员组能够访问下列用户权限：

调试程序
在身份验证之后模拟客户端
管理审核和安全日志

可以在以下位置授予这些权限：
本地安全策略\安全设置\本地策略\用户权限分配

注意：在身份验证之后模拟客户端权限策略设置，请确保策略设置以及本地管理员组中列出了群集服务帐户。如果群集服务帐户未列出，则计算机可能不再具有访问 Windows Management Instrumentation (WMI) 的权限。默认情况下，这些帐户会在身份验证之后模拟客户端权限策略中列出。但是，如果创建组策略设置但不添加群集服务帐户，则本地策略设置将被覆盖，并且 WMI 访问会失败。

SQL Server 企业版和标准版之间的差别

SQL Server的企业版和标准版的License价格差5倍之多，在企业应用中，DBA经常会被这个问题问住，本帖将日常工作实践中遇到到版本问题给出第一手资料，陆续补充……

SQL 2008 镜像数据库：
企业版和标准版都支持镜像数据库，不同的是，企业版可以设置成高性能（异步）模式，标准版只能是高安全（同步）模式。

SQL 2008 压缩备份：
企业版可以压缩备份，标准版不支持压缩备份，但是可以恢复压缩后的备份文件。



在线建索引
ONLINE = { ON | OFF }
指定在索引操作期间基础表和关联的索引是否可用于查询和数据修改操作。默认值为 OFF。

注意：
联机索引操作仅在 SQL Server 2005 Enterprise Edition 及更高版本中可用。

ON
在索引操作期间不持有长期表锁。在索引操作的主要阶段，源表上只使用意向共享 (IS) 锁。这使得能够继续对基础表和索引进行查询或更新。操作开始时，在很短的时间内对源对象持有共享 (S) 锁。操作结束时，如果创建非聚集索引，将在短期内获取对源的 S（共享）锁；当联机创建或删除聚集索引时，以及重新生成聚集或非聚集索引时，将在短期内获取 SCH-M（架构修改）锁。对本地临时表创建索引时，ONLINE 不能设置为 ON。

OFF
在索引操作期间应用表锁。创建、重新生成或删除聚集索引或者重新生成或删除非聚集索引的脱机索引操作将对表获取架构修改 (Sch-M) 锁。这样可以防止所有用户在操作期间访问基础表。创建非聚集索引的脱机索引操作将对表获取共享 (S) 锁。这样可以防止更新基础表，但允许读操作（如 SELECT 语句）。

ESX Open Conole无法连接错误的处理

原有服务器安装的ESX3.02，近日打开时发现无法连接虚拟机的控制台了，提示“unkown MKS event，do you want to try again?”，点确认以后仍然重复报错……

以为是ESX有问题了，于是下载了最新的ESX3.5U3，安装完以后，问题依然。
在虚拟机的目录里查看vmware.log，发现有两行报错：

Feb 11 11:21:49.538: mks| SOCKET 43 recv error 5: Input/output error
Feb 11 11:21:49.538: mks| SOCKET 43 destroying VNC backend on socket error: 5

在网上查了很久，有人说是跟运行vi的主机有关，特别是主机装过一些通讯程序，比如手机、无线、VOIP之类，于是换了一台试试，果然可以打开了。

这个机器上确实装过手机通讯软件，挨个卸载试试～

SQL Server拉取式发布订阅从2000到2008的配置方法

本例特别针对SQL2000实例和SQL2008实例不在同一网段的情况。

一般我们在使用发布订阅时，发布端和订阅端一般都在同一网段的，IP是双向互通的，如果有跨网段的情况发生时，比如发布服务器有固定IP，而订阅服务器在一个局域网内，没有对外固定IP时，解决办法就是配置拉取式(Pull)复制。

基本梗概如下：
1. 在发布端配置发布；
2. 在订阅端执行创建订阅的脚本；
（主要会执行存储过程：sp_addpullsubscription，sp_addpullsubscription_agent）
3. 在发布端再执行脚本。
（主要会执行存储过程：sp_addsubscription）

脚本的生成，可以借用同一网段的机器配置成功发布订阅，然后修改得到。

SQL Server孤儿用户Orphan user问题解决方法

在“logins”下没有abc的帐号，但是建立abc后一直提示错误——“Error 21002: [ SQL-DMO] 用户"xxxxx"已经存在”，这是孤儿用户存在的典型错误，解决方法如下：

EXEC sp_change_users_login 'report' [<=这是保留字,不要乱改.]
这是要求DB给你妳一份login的报告.妳你可以一目了然,Orphan account的名单.

EXEC sp_charge_users_login 'auto_fix', 'theOrphanLoginName'
这是修补Orphan account.

登录事件Windows Event ID 528-Logon Type Codes Revealed

Logon Type Codes Revealed
http://www.windowsecurity.com/articles/Logon-Types.html

Published: Mar 29, 2005
Updated: Mar 29, 2005
Section: Articles :: Misc Network Security
Author: Randall F. Smith

The logon/logoff category of the Windows security log gives you the ability to monitor all attempts to access the local computer. In this article I’ll examine each logon type in greater detail and show you how some other fields in Logon/Logoff events can be helpful for understanding the nature of a given logon attempt


Event IDs 528 and 540 signify a successful logon, event ID 538 a logoff and all the other events in this category identify different reasons for a logon failure. However, just knowing about a successful or failed logon attempt doesn’t fill in the whole picture. Because of all the services Windows offers, there are many different ways you can logon to a computer such as interactively at the computer’s local keyboard and screen, over the network through a drive mapping or through terminal services (aka remote desktop) or through IIS. Thankfully, logon/logoff events specify the Logon Type code which reveals the type of logon that prompted the event.

Logon Type 2 – Interactive
This is what occurs to you first when you think of logons, that is, a logon at the console of a computer. You’ll see type 2 logons when a user attempts to log on at the local keyboard and screen whether with a domain account or a local account from the computer’s local SAM. To tell the difference between an attempt to logon with a local or domain account look for the domain or computer name preceding the user name in the event’s description. Don’t forget that logon’s through an KVM over IP component or a server’s proprietary “lights-out” remote KVM feature are still interactive logons from the standpoint of Windows and will be logged as such.

Logon Type 3 – Network
Windows logs logon type 3 in most cases when you access a computer from elsewhere on the network. One of the most common sources of logon events with logon type 3 is connections to shared folders or printers. But other over-the-network logons are classed as logon type 3 as well such as most logons to IIS. (The exception is basic authentication which is explained in Logon Type 8 below.)

Logon Type 4 – Batch
When Windows executes a scheduled task, the Scheduled Task service first creates a new logon session for the task so that it can run under the authority of the user account specified when the task was created. When this logon attempt occurs, Windows logs it as logon type 4. Other job scheduling systems, depending on their design, may also generate logon events with logon type 4 when starting jobs. Logon type 4 events are usually just innocent scheduled tasks startups but a malicious user could try to subvert security by trying to guess the password of an account through scheduled tasks. Such attempts would generate a logon failure event where logon type is 4. But logon failures associated with scheduled tasks can also result from an administrator entering the wrong password for the account at the time of task creation or from the password of an account being changed without modifying the scheduled task to use the new password.

Logon Type 5 – Service
Similar to Scheduled Tasks, each service is configured to run as a specified user account. When a service starts, Windows first creates a logon session for the specified user account which results in a Logon/Logoff event with logon type 5. Failed logon events with logon type 5 usually indicate the password of an account has been changed without updating the service but there’s always the possibility of malicious users at work too. However this is less likely because creating a new service or editing an existing service by default requires membership in Administrators or Server Operators and such a user, if malicious, will likely already have enough authority to perpetrate his desired goal.

Logon Type 7 – Unlock
Hopefully the workstations on your network automatically start a password protected screen saver when a user leaves their computer so that unattended workstations are protected from malicious use. When a user returns to their workstation and unlocks the console, Windows treats this as a logon and logs the appropriate Logon/Logoff event but in this case the logon type will be 7 – identifying the event as a workstation unlock attempt. Failed logons with logon type 7 indicate either a user entering the wrong password or a malicious user trying to unlock the computer by guessing the password.

Logon Type 8 – NetworkCleartext
This logon type indicates a network logon like logon type 3 but where the password was sent over the network in the clear text. Windows server doesn’t allow connection to shared file or printers with clear text authentication. The only situation I’m aware of are logons from within an ASP script using the ADVAPI or when a user logs on to IIS using IIS’s basic authentication mode. In both cases the logon process in the event’s description will list advapi. Basic authentication is only dangerous if it isn’t wrapped inside an SSL session (i.e. https). As far as logons generated by an ASP, script remember that embedding passwords in source code is a bad practice for maintenance purposes as well as the risk that someone malicious will view the source code and thereby gain the password.

Logon Type 9 – NewCredentials
If you use the RunAs command to start a program under a different user account and specify the /netonly switch, Windows records a logon/logoff event with logon type 9. When you start a program with RunAs using /netonly, the program executes on your local computer as the user you are currently logged on as but for any connections to other computers on the network, Windows connects you to those computers using the account specified on the RunAs command. Without /netonly Windows runs the program on the local computer and on the network as the specified user and records the logon event with logon type 2.

Logon Type 10 – RemoteInteractive
When you access a computer through Terminal Services, Remote Desktop or Remote Assistance windows logs the logon attempt with logon type 10 which makes it easy to distinguish true console logons from a remote desktop session. Note however that prior to XP, Windows 2000 doesn’t use logon type 10 and terminal services logons are reported as logon type 2.

Logon Type 11 – CachedInteractive
Windows supports a feature called Cached Logons which facilitate mobile users. When you are not connected to the your organization’s network and attempt to logon to your laptop with a domain account there’s no domain controller available to the laptop with which to verify your identity. To solve this problem, Windows caches a hash of the credentials of the last 10 interactive domain logons. Later when no domain controller is available, Windows uses these hashes to verify your identity when you attempt to logon with a domain account.

Conclusion
I hope this discussion of logon types and their meanings helps you as you keep watch on your Windows network and try to piece together the different ways users are accessing your computers. Paying attention to logon type is important because different logon types can affect how you interpret logon events from a security perspective. For instance a failed network logon on a server might now be surprising since users must access servers over the network all the time. But a failed network logon attempt in a workstation security log is different. Why is anyone trying to access someone else’s workstation from over the network? As you can see, it pays to understand the security log.