Limit running Powershell script under specific path with AppLocker

Say if you have a management server or a script repository server, sometimes users save/test/run their script from different path, it is different to maintain the script version, and it is hard to determine which script can be removed or kept on the server.

here is a solution,  you can save all scripts in a central directory, and keep script from running in other path with Applocker. let's say my scripts are saved in c:\work:
1. Open "local security policy" by secpol.msc

2. Open "Security Settings" -> "Application Control Policy"->"Applocker"->"Script Rules"

3. Right click "Script Rules", Click "Create New Rule..."

4. Select "Allow", and enter the user name which

5. Select "Path"

6. Enter the script file path "C:\work"

7. Click "Next" if you don't need exception

8. Enter the Name of the Rule

9. Click "Yes" to create default rule if it is the first time you use Applocker

After the rule created, if you run powershell script from other path, for instance "c:\temp", you will get error

Only running powershell script under c:\work is allowed.

Actually no only powershell script, but also other script like(.com,.bat...) can only be run from c:\work,

you can create other rules to meet your requirement.

Set DAC port with a specific number

Usually named instance can uses dynamic port, if firewall setup on the server, you can use SQL Server Configuration Manager to assign a specific port to SQL Server instance. However, there is no UI to reconfigure the DAC port, you need to use regedit.exe to configure a specific port number.

1. Run regedit.exe

2. Open registry key

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLX.InstanceName\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp

"X" is the sql server version number, "InstanceName" is the sql server instance name. for instance, my sql2012 named instance is "SQL2012", the path is like below

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp

3. Set Value "TcpDynamicPorts" to a specific port number

here we set the  "TcpDynamicPorts" to 12345.

4. Restart sql server service, then in errorlog you can find the DAC is monitoring port 12345

2013-04-10 15:30:16.22 Server      Server is listening on [ ::1 <ipv6> 12345].

2013-04-10 15:30:16.22 Server      Server is listening on [ 127.0.0.1 <ipv4> 12345].

2013-04-10 15:30:16.22 Server      Dedicated admin connection support was established for listening locally on port 12345.

5. Validate the connection with DAC

sqlcmd -S tcp:localhost,12345 -E

please note, don't need to use parameter -A in sqlcmd command line.

Use delegated session configuration in Powershell 3.0

Powershell 3.0 has many new feature, and they are useful in some circumstance.

let's say User A log in server A, and he wants to access the resource on Server B, however User A doesn't have permission to access the resource, User B has resource access permission, so User A needs to use User B's credential.

Normally if we want to use remote session to access resource , we can use cmdlets
1. Enter-PSSesssion
2. Invoke-Command.

However we need to provide User B's credential and password when create remote session. There is potential risk, sometimes we don't want to give the password to operators, in that case, we can use delegated session configuration.

Here is the step
1. Register a new session configuration with "-RunAsCredential UserB" on Server B

Register-PSSessionConfiguration -Name DomainAdmin -RunAsCredential xxx\administrator -ShowSecurityDescriptorUI

Here we create a session configuration named "DomainAdmin", and the session will be run as user "xxx\administrator" credential

2. Grant full access to User A
In the next popup window, grant full access to User A

3. Verify the session configuration
you can run "Get-PSSessionConfiguration" to view the all session configuration on Server B.
or you can run "Set-PSSessionConfiguration DomainAdmin -ShowSecurityDescriptorUI" to configure the permission.

4. Now on server A, create remote session with "-ConfigurationName" parameter
Enter-PSSession -ComputerName ServerB -Credential xxx\UserA -ConfigurationName DomainAdmin

here we use UserA's credential to create remote session, and use session configuration DomainAdmin. so we just need provide password for UserA when creating remote session, then we can run as User B's credential on Server B

so when we run whoami, it shows the current user is administrator(User B).

delegated session configuration is easy to use, and very useful especially on system maintenance work.

Compression Backup with BUFFERCOUNT parameter

If you want to make you backup faster, you can try BUFFERCOUNT parameter with compression backup.

From Book Online description:

BUFFERCOUNT = { buffercount | @buffercount_variable }

Specifies the total number of I/O buffers to be used for the backup operation. You can specify any positive integer; however, large numbers of buffers might cause "out of memory" errors because of inadequate virtual address space in the Sqlservr.exe process.
The total space used by the buffers is determined by: buffercount * maxtransfersize.

Below is my testing result, all backups use compression parameter. the data file is 195GB with 11GB used. by using compression, the backup file is 8.4GB

1. No BUFFERCOUNT parameter

BACKUP DATABASE abc TO DISK = 'c:\temp\abc.bak' WITH COMPRESSION

it completed with 88MB/sec throughput, avg cpu% is 45

2. Add BUFFERCOUNT parameter

BACKUP DATABASE abc TO DISK = 'c:\temp\abc.bak' WITH COMPRESSION, BUFFERCOUNT = 50

it completed with 105MB/sec throughput, avg cpu% is 50

Next I tried BUFFERCOUNT = 100, BUFFERCOUNT = 150 and BUFFERCOUNT = 200

here is the result:

based on the diagram upper, when BUFFERCOUNT = 100, Throughput reach the ceiling, comparing with the first command without BUFFERCOUNT parameter(default), we made the backup process 30% faster.

you can also use multiple backup files to reach the same goal. 

3. Test multiple backup files

my sql server has 2 CPU, so I use 2 backup files:

BACKUP DATABASE abc TO 

DISK = 'c:\temp\abc1.bak',

DISK = 'c:\temp\abc2.bak'

WITH COMPRESSION

it completed with 115MB/sec throughput. avg cpu 60%.

4. Test multiple backup files with BUFFERCOUNT parameter

BACKUP DATABASE abc TO 

DISK = 'c:\temp\abc1.bak',

DISK = 'c:\temp\abc2.bak'

WITH COMPRESSION, BUFFERCOUNT = 50

based on the diagram upper, there is only 5% performance difference between using BUFFERCOUNT and no BUFFERCOUNT.

Although BUFFERCOUNT makes higher CPU %, the backup always runs during off business, so if you can afford the higher CPU% penalty, it is worth to try BUFFERCOUNT. Please test the appropriate BUFFERCOUNT value based on your environment. 

Reference:

http://sqlcat.com/sqlcat/b/technicalnotes/archive/2008/04/21/tuning-the-performance-of-backup-compression-in-sql-server-2008.aspx

Get Perfmon data with Powershell

Sometimes you just want to have a look at the system performance, or you feel tired with log on the server, open the perfmon and configure the performance counter, perhaps you want to run a simple command to get the performance data from remote server, here we have a convenient way check the system performance counter on remote server:

Powershell Command : Get-Counter
http://technet.microsoft.com/en-us/library/hh849685.aspx

However, read the data from Get-Counter is not a easy way, so I wrapped it up with several functions and put them into a powershell module : osperfmon.psm1
you can download it from
https://docs.google.com/file/d/0B4Xde9z-OME1ZHhEZG8tS3h4Sms/edit?usp=sharing

Here is the steps to run it
       1.Setup the module.

• Download the osperfmon.psm1, Copy it to local drive
• Open powershell window, and import the module

                    Import-Module .\osPerfmon.psm1

              

• List the available function

                   Get-Command -Module osperfmon

                 

      2. Get Function detail.
       You can get the function help with "Get-Help -detailed", for instance
       Get-Help Get-CPUPerf -Detailed

Here is the sample for each function:
1. Get-CPUPerf

   2. Get-DiskPerf

 3. Get-MemoryPerf

4. Get-NetPerf

5. Get-ProcessPerf

Each function has several parameter, like -SortBy, -Top. you can customize it with your demand.

your suggestion and advice are welcomed. thanks  

"Auto update statistics" option on tempdb

"Auto update statistics" option is enabled by default on tempdb, if it is disabled, you may get trouble in some case.Today when I tested script, I found the index created on the temp table didn't work because of "Auto update statistics" disabled.

here is the script.

USE [master]
GO
ALTER DATABASE [tempdb] SET AUTO_UPDATE_STATISTICS OFF WITH NO_WAIT
GO
USE [tempdb]
GO
dbcc freeproccache
GO
Create table #mytemp1(a int, b int)
GO
create  index temp_inx1 on #mytemp1(a)
GO
declare @int int
set @int=1
while @int <10000
begin
insert into #mytemp1 values(@int, @int+1)
--insert into #mytemp2 values(@int, @int+1)
set @int+=1
end

GO
set statistics profile on
GO
select b from #mytemp1 where a=50
GO
set statistics profile off
GO

and you can see the script use table scan instead of index seek, because we created an index on a, table scan is not we wanted.

let's enable the "Auto update statistics", and do the test again

USE [master]

GO

ALTER DATABASE [tempdb] SET AUTO_UPDATE_STATISTICS on WITH NO_WAIT

GO

USE [tempdb]

GO

dbcc freeproccache

GO

Create table #mytemp1(a int, b int)

GO

create  index temp_inx1 on #mytemp1(a)

GO

declare @int int

set @int=1

while @int <10000

begin

insert into #mytemp1 values(@int, @int+1)

--insert into #mytemp2 values(@int, @int+1)

set @int+=1

end

GO

set statistics profile on

GO

select b from #mytemp1 where a=50

GO

set statistics profile off

GO

drop table #mytemp1

this time we got index seek, this is because:

when "Auto update statistics" is enabled, sql server will check if statistics is stall before generation query plan. since we inserted 1000 rows after index created, the statistics is stall. then sql server will update the statistics, so sql server can get correct statistics and select index seek instead of table scan as query plan.

if you are working with big temp table object, be careful for the "Auto update statistics" on tempdb. 

Got "Cannot generate SSPI context" error message after changing sql service account

Today I changed a sql server service startup account during testing, then when I tried to connect sql server with powershell, I got error message below:

The target principal name is incorrect.  Cannot generate SSPI context."


Here is the troubleshooting step I used
1. Rollback service startup account to old one, then the powershell script work. so it should be the issue with the new account.

2. Start sql server service again with new account. check sql server error log, then found error message in it


2013-01-20 00:53:36.49 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.

the error indicted sql server service can not register SPN during startup.

3. search the error code 0x2098, found link below

http://msdn.microsoft.com/en-us/library/ms681390(VS.85).aspx

ERROR_DS_INSUFF_ACCESS_RIGHTS

8344 (0x2098)

Insufficient access rights to perform the operation.

4. grant "Read servicePrincipalName" and "Write servicePrincipalName" permission to new account.

http://support.microsoft.com/kb/811889

for window 2012 AD, it is a little bit different

1. Click Start, click Run, type Adsiedit.msc, and then click OK.
2. In the ADSI Edit snap-in, expand Domain [DomainName], expand DC= RootDomainName, expand CN=Users, right-click CN= AccountName, and then click Properties. 
3. In the CN= AccountName Properties dialog box, click the Security tab.
4. On the Security tab, click Advanced.
5. In the Advanced Security Settings dialog box, select one of "SELF"'s row just like the pic below

     6. Click Edit, Open Permission Entry dialog box.

     7. Make sure Pricipal is "SELF", Type is "Allow" and "Applied to" is "This Object Only", in Properties section, select the properties below:

Read servicePrincipalName

Write servicePrincipalName

     8. Click OK three times, and then exit the ADSI Edit snap-in.

after restart the sql server service, make sure the spn resgister correctly:

2013-01-20 03:08:27.20 Server      The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/machinename.domainname.com ] for the SQL Server service.