Search results matching tags 'Database Administration', 'SQL Server 2008', and 'SMO'

Change SQL Servers Authentication Mode with PowerShell

AllenMWhite — Thu, 19 May 2011 20:22:00 GMT

Lately I've been working on scripts to check and set security and configuration settings for SQL Server using PowerShell. One of the settings that I normally set and forget at install time is the Authentication Mode setting. Best practices suggest that you set this to Windows Authentication, but my experience has been that it's always better to set it to Mixed mode, and set a very strong password for the sa account.

So, what if we want to change it after the fact? Well, it's a registry setting. Management Studio allows you to make that change via the Security page in Server Properties, but I prefer scripting when setting configuration settings. The setting is exposed in SMO (Server Management Objects), however, and we can check the setting using PowerShell. (I'm going to assume you're either running SQLPS.exe or you've already loaded the SMO libraries. If you don't know what I'm talking about, check here.)

# Connect to the instance using SMO
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'MyServer\MyInstance'
[string]$nm = $s.Name
[string]$mode = $s.Settings.LoginMode

write-output "Instance Name: $nm"
write-output "Login Mode: $mode"

What the script returns is the instance name, and the authentication mode it's using. The property in SMO is called LoginMode, and can have one of four values:

Integrated - Windows Authentication
Mixed - Mixed Mode
Normal - SQL Server Only Authentication
Unknown - Undefined (and no, I haven't tried it.)

I sometimes encounter a system that was set to Integrated, and I want to change it to Mixed mode, because I like to have that safety net of sa if something goes wrong. Here's how I do that:

#Change to Mixed Mode
$s.Settings.LoginMode = [Microsoft.SqlServer.Management.SMO.ServerLoginMode]::Mixed

# Make the changes
$srv.Alter()

Once that's done the server does need to be restarted, and I need to go in and set the sa password to something VERY strong right away. But now the server is set to the authentication mode I prefer, and it's a lot easier (in my mind) than going into the registry to do it.

Allen

Discover Facets and Properties in PowerShell

AllenMWhite — Mon, 27 Sep 2010 14:56:00 GMT

When you're trying to set up scripts to deploy policies to your servers (because you ALWAYS automate with PowerShell, right?), it can be sometimes difficult to determine exactly what facets are there, and when you do know the facet name, what properties are available to you.

First, in a native PowerShell window you'll need to load the assemblies with the Policy-Based Management objects. Because PBM was originally called Dynamic Management Framework, the DLL carries the original name, so the following two lines will load the correct assemblies:

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Dmf')
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SQLServer.Management.Sdk.Sfc')

The next thing to do is to connect to our SQL Server instance, and load the instance's PolicyStore object, which is where the existing policies are kept.

$conn = New-Object Microsoft.SQlServer.Management.Sdk.Sfc.SqlStoreConnection("server=SQLTBWS\INST01;Trusted_Connection=true");
$PolicyStore = New-Object Microsoft.SqlServer.Management.DMF.PolicyStore($conn);

Now we can start exploring. We can pull the list of facets by referencing them within the PolicyStore object, and then store them in a variable called $facets. We can then pipe that variable into a select-object cmdlet and get the name and description of the available facets:

$facets = [Microsoft.SqlServer.Management.Dmf.PolicyStore]::Facets
$facets | select Name, Description

Now we know what facets are available. Let's say we want to set up some database maintenance policies. We can figure out what properties are available to us by creating a facet object variable for the 'IDatabaseMaintenanceFacet' facet. Then we grab the properties list from it by setting a variable to the facet's FacetProperties object. Finally we can pipe the property list through the select-object cmdlet to see the name and property type of each property for the facet:

$f = $facets['IDatabaseMaintenanceFacet']
$fp = $f.FacetProperties
$fp | select Name, PropertyType

Once we have this information we can use it to build conditions, and from those conditions, the policies we want to establish on our servers.

Allen

PASS PowerShell/SQL Server PreCon

AllenMWhite — Thu, 19 Aug 2010 19:17:00 GMT

PowerShell is an amazing tool to help you automate your administrative processes. There are a lot of books and online sources to help you learn PowerShell, but how do you learn how to use it with SQL Server? Simple, just sign up for my PASS PreCon session. Here's the outline:

Use PowerShell to Get the Most out of SQL Server

Module 1: Introduction to PowerShell

Cmdlets
Aliases
The Pipeline
Variables
Objects
Control Flow
Functions
Modules
Error Handling
Command-line Arguments

Module 2: Introduction to SMO

Introduction to the SMO Library
SQLPS.exe - The SQL Server Mini-Shell
The SMO Object Model

Module 3: Backup and Recovery

Backup
Restore

Module 4: Automating & Maintaining Databases

Automating Index Maintenance
Automating DBCC

Module 5: Importing, Exporting and Transforming Data

ADO.NET
Invoke-SQLCMD
Importing and Exporting using CSV Files
Importing and Exporting using XML Files

Module 6: Manipulating Database Objects

Creating and Deleting Schemas
Creating, Modifying and Deleting Tables
Creating, Modifying and Deleting Triggers
Creating, Modifying and Deleting Indexes
Creating, Modifying and Deleting Views

Module 7: Managing the Enterprise

Registering Servers
Creating a Central Management Server
Connecting to a Central Management Server
Running Scripts Against Multiple Servers
Defining Policies for Policy-Based Management
Evaluating Policies for Policy-Based Management

Module 8: Profiling and Monitoring SQL Server

Gathering WMI Information
Gathering Performance Counters
Setting up a Server Side Trace
Monitor Log Growth

Module 9: Configuring and Tuning SQL Server

Setting Configuration Options
Setting Windows Mode or Mixed Mode

As you can see, we're going to spend the first part of the day learning the basics of PowerShell, but the focus is on how you can manage your own environment using this powerful language, so you can spend your time solving problems instead of the constant point-and-click drudgery of typical DBA tasks.

The all-day session is reasonably priced, and you'll come away with scripts to help you put this tool into use right away. Check out the session details at Use PowerShell to Get the Most out of SQL Server.

See you there!

Allen