Using implicit PowerShell remoting to import remote modules

Implicit remoting is one of PowerShell's "best-kept secrets." Learn how you can load modules from remote servers into your local system runspace to use the commands natively.

Source : 4sysops

Update multiple SQL Server systems with PowerShell

In this article, I'll show you how to create a tool in PowerShell that will allow you to update multiple SQL servers in one swath with a single command.

Source : 4sysops

MS15-011 & MS15-014: Hardening Group Policy

Today we are releasing MS15-011 & MS15-014 which harden group policy and address network access vulnerabilities that can be used to achieve remote code execution (RCE) in domain networks. The MS15-014 update addresses an issue in Group Policy update which can be used to disable client-side global SMB Signing requirements, bypassing an existing security feature built into the product. MS15-011 adds new functionality, hardening network file access to block access to untrusted, attacker controlled shares when Group Policy refreshes on client machines. These two updates are important improvements that will help safeguard your domain network.

Source : MS15-011 & MS15-014: Hardening Group Policy - Security Research & Defense - Site Home - TechNet Blogs

Solving SQL Server High CPU with IIS Request Filtering

The other day I was troubleshooting 100%  CPU utilization on a SQL Server 2008 database server. The server had 100 or so databases of varying sizes however none were larger than a few hundred MB and each database had a corresponding web site on a separate web server.  Since the server hosted quite a few databases the high CPU needed to be resolved quickly because it was causing issues for everyone.  High CPU on a database server can often be symptomatic of a issues occurring outside the server. In this case the real issue was in fact being caused by a SQL Injection attack on a web server.

Source : Solving SQL Server High CPU with IIS Request Filtering | Peter Viola

SQL Injection Hacker Attacks Are On The Rise. Here's How To Defend Your Servers

Last week, a hacker group claimed that it breached computer systems at
100 major universities. Team GhostShell gained access to servers at
Stanford, Harvard, and the University of Michigan, among others. The
technique used, SQL injection, is not new or complex, but reportedly
it's becoming increasingly common. Here's a quick guide to defending your servers.

 Read more : ReadWriteWeb

Dedicated Administrator Connection (DAC) in SQL Server 2008 R2

In Microsoft Windows, we have Safe Mode. In SQL Server 2008, we have the Dedicated Administrator Connection, or DAC. We can use DAC to obtain emergency access to a SQL Server 2008 database instance in cases where the instance is blocked up, locked up, or just plain hosed.

Full article : 4sysops.com

Blocking Automated SQL Injection Attacks

SQL injection attacks have been increasing over the last three years, mainly because of automated tools. Since these automated attacks were first noticed in December 2007, very little has changed in the way that they work. Attackers use automated tools to query search engines for interesting URLs and then submit various SQL injection payloads to each. The goal is to inject malicious JavaScript into all string columns in SQL database tables.

Microsoft has provided guidance (http://blogs.technet.com/swi/archive/2008/05/29/sql-injection-attack.aspx) and some tools (www.microsoft.com/technet/security/advisory/954462.mspx) to combat these attacks. Microsoft has also started tracking these automated SQL injection attacks during the last one year and provides the information in the Microsoft® Security Intelligence Report.

Today I would like discuss another technique that one can use to block automated SQL injection attacks against web applications that use Microsoft SQL Server® as the back-end processor. (Note: These attacks exploit vulnerabilities in web applications; there are no known security vulnerabilities in Microsoft SQL Server). Before I describe the technique, I would like to reiterate that using parameterized queries is the best way to mitigate SQL injection vulnerabilities in web applications. You can read this Quick Security Reference: SQL Injection that details various classes of SQL injection vulnerabilities and how to address them in the design, development, and testing phases.

Source : Microsoft Security Tips

Microsoft PHP driver for SQL Server 2.0 released

The SQL Server team just announced the release of their latest PHP driver for SQL Server. Details about the release can be found at http://blogs.msdn.com/b/sqlphp/archive/2010/08/04/microsoft-drivers-for-php-for-sql-server-2-0-released.aspx.

A major highlight of this release has been the introduction of PDO drivers for SQL Server. This means the PHP application developer now need not care about database but rather focus on the application code.

Source : The Official Microsoft IIS Site

Cross-site Scripting & SQL Injection quick reference

The Microsoft Security Development Lifecycle web site shows two interesting documents :

Quick Security References: Cross-site Scripting

Quick Security References: SQL Injection

You can download those at www.microsoft.com/downloads

Disk Partition Alignment Best Practices for SQL Server

Disk partition alignment is a powerful tool for improving SQL Server performance. Configuring optimal disk performance is often viewed as much art as science. A best practice that is essential yet often overlooked is disk partition alignment. Windows Server 2008 attempts to align new partitions out-of-the-box, yet disk partition alignment remains a relevant technology for partitions created on prior versions of Windows.

This paper documents performance for aligned and nonaligned storage and why nonaligned partitions can negatively impact I/O performance; it explains disk partition alignment for storage configured on Windows Server 2003, including analysis, diagnosis, and remediation; and it describes how Windows Server 2008 attempts to remedy challenges related to partition alignment for new partitions yet does not correct the configuration of preexisting partitions.

 

For more information, please refer to the Disk Partition Alignment Best Practices for SQL Server whitepaper

Source : Bink.nu

Enforce Windows Password Policy on SQL Server Logins

If users choose to use SQL login to connect to SQL Server rather than using NT authenticating, it is worth to remind that SQL server does provide the option of enforcing window password policy on SQL logins.

Source : SQL Server Security

SQL Server 2005 Database Mail in cluster (updated)

I have updated my post about SQL Server 2005 Database Mail in a cluster. You should check it.

SQL Server 2005 Service Pack 3 Released

Service Pack 3 for Microsoft SQL Server 2005 is now available.

Download and infos : www.microsoft.com/downloads/

SQL Servers Blogs

There is a list of all blogs related to SQL Server at Microsoft. You will find it at Technet.

RML Utilities for SQL Server

Do you know which databases or applications are using the most resources on your server? How will a service pack upgrade, configuration change or application change affect your production SQL Server? The RML Utilities for SQL Server provide you a set of tools and processes to answer these questions and much more.

Downloads :

• RML Utilities for SQL Server (x86)
• RML Utilities for SQL Server (x64)

SQL Index defrag and maintenance tasks for Search

Hi all, this topic is an area that has caused me much pain and work.  My goal for this was to follow the recommended SQL guidelines while minimizing the impact that these maintenance jobs have on Crawling and Queries.  We know from the SQL Monitoring an I/O post that Search is extremely I/O intensive .  As it turns out so is all of the regular maintenance that SQL recommends, so finding the right balance between the two is an interesting scheduling task.

Read the complete post : blogs.msdn.com/enterprisesearch

SQL Server : error detaching msdb

I received the following trying to detach the msdb database in SQL Server 2005 :

Cannot detach an opened database when the server is in minimally configured mode

I was trying to move msdb according to kb224071. I finally found why.

When I opened my query in SQL Server Management Studio (SSMS) I was connecting to msdb database by default. Event if I do a use master; after that, it was too late, I always got an error doing a sp_detach_bd 'msdb';

So here's the trick :

• Click new query on the tool bar of SSMS
• Click on the Options >> buton in the Connect to Server dialog
• Select the Connection Properties tab
• Type master in the Connect to database field
• Click Connect

Et voilà ! You will now be able to detach the msdb database.

Note : Of course this will only work if you follow kb224071 and start SQL Server with the -c -m -T3608 startup options.

Can't logon to SQL Server 2005 Cluster in single-user mode

Last night I spent a few hours trying to follow the kb224071's instructions to move the MSDB system database but was always getting the following error trying to connect with the -c -m -T3608 startup options :

Login failed for user '******'. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: *.*.*.*]

But nobody else was using the database !

Seems that I was wrong. When I looked again at my error log I saw this line :

Login succeeded for user 'NT AUTHORITY\SYSTEM'. Connection: trusted. [CLIENT: *.*.*.*]

Then I tried the same thing in virtual without a cluster and everything worked fine, so I got a little suspicious about the cluster service.

So here's what I've done and it worked :

1. Put all SQL Server resources offline in the cluster (except the storage of course).
2. execute :
   NET START "SQL SERVER (instance)" /c /m /T3608
3. You will now be able to connect to your database in single-user mode.
4. When your finished, execute the following command and restart all SQL Server resources in the cluster :
   NET STOP "SQL SERVER (instance)"

I'm not an SQL engineer but it looks to me like the Cluster Service opened a connection to the SQL Server.

Hope this could help someone else.

SQL Server 2005 Database Mail in cluster

Updated in december 2008 (see at the end)

I've been playing with Database Mail in my SQL 2005 Cluster for a few days now. I always get the following errors :

Database Mail Log:
Log Database Mail (Database Mail Log)
Last Modified By sa
Message : Activation failure.

EventViewer Application Log: Event Type: Information
Event ID:   9724
User:       N/A
Description: The activated proc [dbo].[sp_sysmail_activate] running on queue msdb.dbo.ExternalMailQueue output the following: 'Error executing extended stored procedure: Invalid Parameter'

Last night I found a solution and I though it would be a good idea to share it.

As you know SQL Server use an external program to process mail : DatabaseMail90.exe. As I understand it, when SQL launch DatabaseMail90.exe it connect to the msdb database and look in the system tables for it's configuration and for messages to process.

First Problem : DatabaseMail90.exe connection

Looks like DatabaseMail90.exe is trying to connect to SQL using Localhost or ComputerName as it's SQL Server name. In a cluster you can't do that, you should use the Virtual SQL Server Name as define in your Cluster resources. Can we change that ? Not really.

But there is a workaround. Microsoft added a new feature in SQL SP2 to allow you to send mail from a remote server. We are going to use that feature to make DataBaseMail90.exe connect to the Virtual SQL Server (Cluster).

First we have to create the text file DataBaseMail90.exe.config in the \MSSQL\Binn folder of your SQL Instance (ex: \Program Files\Microsoft SQL Server\MSSQL.1\MSSQLSERVER\MSSQL\Binn). with the following content :

<configuration>
    <appSettings>
        <add key="DatabaseServerName" value ="VirtualServerName\InstanceName" />
        <add key="DatabaseName" value ="msdb" />
    </appSettings>
</configuration>

Replace VirtualServerName\InstanceName by the valid names from your environment.

Don't forget your are in a cluster, so you should copy that new file on every node of your cluster.

Next we have to modify the DatabaseMail config to tell SQL Server to use the new file. We'll do that by running the following code in SQL Server Management Studio :

USE msdb;
GO
INSERT INTO [msdb].[dbo].[sysmail_configuration]
   (
   [paramname]
   ,[paramvalue]
   ,[description]
   )
VALUES
   (
   N'ReadFromConfigurationFile'
   ,N'1'
   ,N'Send mail from mail server in configuration file'
   );
GO

Second problem : the 'sp_sysmail_activate' stored procedure

'sp_sysmail_activate' is the procedure that launch the DatabaseMail90.exe process. Normally when you updated your SQL Server to SP2 that procedure got updated to support the new DataBaseMail.exe.confg file. Don't ask me why but when you are in a cluster this is not the case (thanks FreemanRU for the tip).

So you'll have to update it by yourself. I have taken a copy of the 'sp_sysmail_activate' procedure from a working SQL server (non-cluster) SP2 with CU8 (build 3257). You can simple run the code in SQL Server Management Studio :

USE [msdb]
GO
/****** Object:  StoredProcedure [dbo].[sp_sysmail_activate]    Script Date: 08/13/2008 11:59:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- sp_sysmail_activate : Starts the DatabaseMail process if it isn't already running
--
ALTER PROCEDURE [dbo].[sp_sysmail_activate]

AS
BEGIN
    DECLARE @mailDbName sysname
    DECLARE @mailDbId INT
    DECLARE @mailEngineLifeMin INT
    DECLARE @loggingLevel nvarchar(256)
    DECLARE @loggingLevelInt int  
    DECLARE @parameter_value nvarchar(256)
    DECLARE @localmessage nvarchar(max)
    DECLARE @readFromConfigFile INT
    DECLARE @rc INT

    SET NOCOUNT ON
    EXEC sp_executesql @statement = N'RECEIVE TOP(0) * FROM msdb.dbo.ExternalMailQueue'

    EXEC @rc = msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'DatabaseMailExeMinimumLifeTime',
                                                        @parameter_value = @parameter_value OUTPUT
    IF(@rc <> 0)
        RETURN (1)

    --ConvertToInt will return the default if @parameter_value is null or config value can't be converted
    --Setting max exe lifetime is 1 week (604800 secs). Can't see a reason for it to ever run longer that this
    SET @mailEngineLifeMin = dbo.ConvertToInt(@parameter_value, 604800, 600)

    EXEC msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'ReadFromConfigurationFile',
                                                  @parameter_value = @parameter_value OUTPUT
    --Try to read the optional read from configuration file:
    SET @readFromConfigFile = dbo.ConvertToInt(@parameter_value, 1, 0)

    --Try and get the optional logging level for the DatabaseMail process
    EXEC msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'LoggingLevel',
                                                  @parameter_value = @loggingLevel OUTPUT

    --Convert logging level into string value for passing into XP
    SET @loggingLevelInt = dbo.ConvertToInt(@loggingLevel, 3, 2)
    IF @loggingLevelInt = 1
       SET @loggingLevel = 'Normal'
    ELSE IF @loggingLevelInt = 3
       SET @loggingLevel = 'Verbose'
    ELSE -- default
       SET @loggingLevel = 'Extended'

    SET @mailDbName = DB_NAME()
    SET @mailDbId   = DB_ID()

    EXEC @rc = master..xp_sysmail_activate @mailDbId, @mailDbName, @readFromConfigFile,
    @mailEngineLifeMin, @loggingLevel
    IF(@rc <> 0)
    BEGIN
        SET @localmessage = FORMATMESSAGE(14637)
        exec msdb.dbo.sysmail_logmailevent_sp @event_type=3, @description=@localmessage
    END
    ELSE
    BEGIN
        SET @localmessage = FORMATMESSAGE(14638)
        exec msdb.dbo.sysmail_logmailevent_sp @event_type=0, @description=@localmessage
    END

    RETURN @rc
END

There's only one final step, you should restart the mail process with the follwing SQL command :

EXEC msdb.dbo.sysmail_start_sp;

You could always restart the SQL Server if you prefer.

You should now be able to send a test email from the Database Mail object in SQL Server Management Studio from your SQL Server 2005 SP2 in a cluster.

December 2008 update :

I missed something, in the ‘Second problem :’, ‘sys_mail_activate’ is not the only stored procedure that you have to update. There is a few others :

• dbo.sp_ExternalMailQueueListener


• dbo.sp_RunMailQuery


• dbo.sp_send_dbmail


• dbo.sp_sysmail_activate


• dbo.sysmail_delete_mailitems_sp


• dbo.sysmail_delete_profile_sp


• dbo.sysmail_update_account_sp


• dbo.sp_ProcessResponse


• dbo.sp_validate_user

I’ve created a script to update those procedures to the same level as SQL Server 2005 SP2 with Cumulatives Updates 8. You can download my script here.

May I suggest you backup the msdb database before playing with dbmail procs.

WebCast: Microsoft SQL Server 2005 Failover Clustering on Windows Server 2008

This Support WebCast focuses on how to plan, implement, and administer a Microsoft SQL Server 2005 failover cluster on Windows Server 2008. This session provides step-by-step instructions about how to install SQL Server 2005 clustered instance on a Windows Server 2008 cluster. It also discusses the options you can use to move SQL Server 2005 failover cluster from Windows Server 2003 to Windows Server 2008.

Details: http://support.microsoft.com/default.aspx?kbid=953170

Webcast Link: http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?culture=en-US&EventID=1032379512

Source : blogs.msdn.com/clustering