Database Administration

Random Password Script Powershell

I was recently asked if I could write a script that would create a random password based on a word list. The password needs to have a capital letter, a special character, and a number. The password also has to be made up of two seperate words that are in the word list.

my words.csv file is formatted as so

Number, Word

Here is what I did.

$symbols = “(”, “!”, “@”, “#”, “$”, “%”, “^”, “&”, “*”, “_”, “+”, “=”, “?”, “/”, “~”, “;”, “:”, “,”, “<”, “>”, “\”, “)”, “.”
$words = Import-Csv “\\path to word list\words.csv”
$max = $words.Count

$first = New-Object system.Random
$1value = $first.next(0, $max)
$firstword = $words[$1value].word
$firstword = $firstword.toupper()

Start-Sleep -milliseconds 20
$second = New-Object system.Random
$2value = $second.next(1, 23)
$special = $symbols[$2value]

Start-Sleep -milliseconds 300
$third = New-Object system.Random
$3value = $third.next(0, $max)
$secondword = $words[$3value].word

Start-Sleep -milliseconds 230
$num = New-Object system.Random
$4value = $num.next(0, $max)
$4value = $words[$4value].number

$newpass = “$firstword$special$secondword$4value”

Echo “`n”
$newpass
Echo “`n”

So as you can see I am using the word list to pull the two words as well as the number value. In order to get my capital I am using the toupper() function on the first word, and for the special character I create an array of characters and then pick one to use.

Any comments or questions please let me know.

Add a AD Group to MSSQL Server and add to a role using Powershell

In SQL Server 2005 the Builtin Administrators group is a login, by default, to your MSSQL Server Instance and is also a member of the sysadmin role. Well this has been changed in SQL Server 2008 with good reason. I decided that I would like to add a group for my DBA Group to every SQL Server in the company and also add this group to the sysadmin role. This way I do not need to have the builtin administrators group be a member of the sysadmin group or even a login to the server. Here is how I did it.

$servers = Import-Csv “csv listing of all servers”
#########################################################
foreach ($entry in $servers)
{
    $torp = $entry.TorP
    $mon = $entry.monitor
    $machine = $entry.server
    $errorlog = $entry.errorlog
    $os = $entry.os2000
    $iname = $entry.Instance
    if ($iname -eq “Null”)
    {
        $instance = “$machine”
    }
    else
    {
        $instance = “$machine\$iname”
    }
    if ($torp -eq “Prod”)
    {
        $ServerType = “Production”
    }
    else
    {
        $ServerType = “Test”
    }
    $instance = $instance.toupper()
#########################################################

[System.Reflection.Assembly]::LoadWithPartialName(’Microsoft.SqlServer.SMO’) | out-null   
# Create an SMO connection to the instance
$s = New-Object (’Microsoft.SqlServer.Management.Smo.Server’) $instance

$logins = $s.Logins
$query = “CREATE LOGIN $sqladmin FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]“
$dba = $logins | where{$_.Name -eq $sqladmin}
#$dba
if ($dba -eq $null)
{
Echo “DBA Group does not exist.`n Adding Group”
Invoke-Sqlcmd -ServerInstance $instance -Query $query
$logins = $s.Logins
$dba = $logins | where{$_.Name -eq $sqladmin}
if ($dba -ne $null)
{
Echo “`n Group Created”
$dba.name
}
}
else
{
Echo “Group already exists.”
$dba.name
}
if ($dba -ne $null)
{
$sroles = $s.Roles
foreach ($srole in $sroles | where{$_.name -eq “sysadmin”})
    {
    $srole.AddMember($sqladmin)

}

This script walks through the list of all my instances and adds the login using sqlcmd and then also adds that new group to the sysadmin role. Let me know if you have any questions or need anything at all.

Migrating SQL 2005 Reporting Services

I have been asked to help with Migrating a SQL Server 2005 Reporting Services server from one host to another. I set up a couple VM’s and away I went. I found that it is pretty straight forward though. I did run into a couple little hickups along the way but nothing to dificult. I did have a problem though, after I attached the database on the new server and brought up the reporting services configuration manager. I could not get the server to initialize. To resolve the issue I had t delete the encryption keys. Then I was able to initialize the server but I could not get any of my reports to run. Kept getting error about connection string not being initialized. I then changed the encryption keys in the configuration manager and all started working again. One other thing is to make sure that you have the RSExecRole setup. Here is a link on migrating a reporting services server.

 http://msdn.microsoft.com/en-us/library/…

Another New Language

My team is going to be developing a new application that will handle all of our monitoring. Currently that includes the monitoring of Sybase Servers, Oracle Instances, and MSSQL Servers. We will also be adding another Database Type that will house all of the Monitoring data. We will be using a MYSQL backend for this application. This application will be developed to run on a unix host so I need to be able to connect to my MSSQL servers from the unix host and insert rows of data back into the MYSQL database. To do this I think that we have decided on Perl for the language of choice. So I now get to go learn Perl. I am excited to learn another language as I am of the befleif that the more tools I have in my box the better off I will be. I will be starting my educational journey of Perl at

 http://www.perl.com/pub/a/2000/10/begper…

I am looking forward to this project and I will keep you up to date on the progress and features.

SQL Server 2008 Learning

I found this nice article that I thought I should pass on regarding Learning SQL Server 2008.

 http://searchsqlserver.techtarget.com/ge…

The article talks about key features and enhancments from SQL Server 2005, BI, Security, and more good stuff. Check it out and enjoy.

Powershell Add a Login to SQL Server

I have a need to write a script that will check to see if a Windows Group exists on a server instance and if not then add it. I was attempting to do this using Powershell and SMO, but I was not really having any luck with that. I changed modes and I have decided to use the invoke-sqlcmd commandlet to get the task done. I use SMO to determine if the group that I want is on the server. If not then I create it. Like so:

$instance = “server\instance”

[System.Reflection.Assembly]::LoadWithPartialName(’Microsoft.SqlServer.SMO’) | out-null
# Create an SMO connection to the instance
$s = New-Object (’Microsoft.SqlServer.Management.Smo.Server’) $instance
#$s | Get-Member -memberType Property
#$s.logins | Get-Member
$logins = $s.Logins
$query = “CREATE LOGIN [domain\group name] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]“
$dba = $logins | where{$_.Name -eq “domain\group name”}
if ($dba -eq $null)
{
Echo “DBA Group does not exist.`n Adding Group”
Invoke-Sqlcmd -ServerInstance $instance -Query $query
$logins = $s.Logins
$dba = $logins | where{$_.Name -eq “PNI\PNI SQL SYSTEMS DBAS”}
if ($dba -ne $null)
{
Echo “`n Group Created”
$dba.name
}
}
else
{
Echo “Group already exists.”
$dba.name
}

Not to hard and it is nice to be able to use SMO to check for the login and then Powershell to create it.

Update sysindexes table in Sybase 12.5.4

I am moving a Sybase instance from one host to another. I have done all the installation and patching and I have loaded all the databases with little to no issues. I ran into a big issue when attempting to change to indexes in sysservers to allow duplicate rows. I had to go and find out what the binary value for the setting that I wanted was and then find out what column held that value. I determined, from looking at my current instance, that I needed the Status colunm to be changed to 64. I then verified this by looking here at the definitions for the table.

 http://infocenter.sybase.com/help/index….

I attempted droping the indexes so that I could just recreate them but you are not able to use Drop Index when working with system tables. That is when I decided to find the value and the column to update. So then I ran a query:

Select name, status from sysindexes

where name = ‘csysservers’

and status = 146

go

This returned on row for me and I thought now I can run the update

update sysindexes

set status = 40

where name = ‘csysservers’

and status = 146

go

Now I get the following error:

Cannot update more than 1 sysindexes row at a time.

Well this did not make sense at all. When I run the select I only get one row returned. But I took the advice of the error and modified my where clause to include more columns and then I was able to make the updates that I needed.

MR. Denny has a webcast

I just saw this and I am looking forward to attending. MR. Denny is a great SQL Resource and I am sure that I will learn new things during this webcast. Here is a link to his blog entry about the webcast.

 http://itknowledgeexchange.techtarget.co…

I suggest you check it out and learn something new from a knowledgable SQL Server DBA.

MSSQL Server Renaming Physical Host

Recently I had a server that hosts a MSSQL 2005 SQL Server named instance and the Windows group decided that the name of the server had to be changed. I thought that this may cuase an issue since the MSSQL Server name is ‘hostname\instancename’. I did a bit of research and found that a solution is available for this problem. At first I thought that I may have to reinstall a new instance of SQL Server in order to get the name correct. I did not want to go thru all of that though.

I found that MS has a couple of Stored Procs that will help with this problem.

sp_dropserver ‘hostname\instancename’ –of the old server\instancename

sp_addserver ‘hostname\instancename’, ‘local’ –of the new server\instancename

I found that when I did a select @@servername I still got the old server name returned. I also found that when I did a sp_helpserver I got the new name. I decided I would restart the server to see if that would update the @@servername variable. I right clicked on the server and said restart. After it cam back up I got a ‘NULL’ from select @@servername. I did not understand why and really still do not. I was able to resolve the issue by going to the services.msc and restarting the SQL Server Service from there. Now I see the new server name from select @@servername as well as sp_helpserver.

Finding what account a Service is running as

Yesterday my boss came to me asking if I knew what server an account was running on. I know that the account is used as a SQL Server service account because of the naming convention that was used. I also know that the account is being used as I used the Quest Software cmdlet get-qaduser to query AD for the account and get the last logon time. Now I just needed to find out what server it was running on. With the account naming conventions that we use this normally would not be an issue but in this case it was as the server that it should have been used on no longer exists. The server had been virtualized and now has a new name. So I put together a script that would identify the server for me. I had the script read in all of my servers from my sqltab file that I have talked about before, and list all of the sql server services and the accounts that they were running as for each server. Here is a nice little one liner that will get you a listing of all sql server services on a server and the account that they are running as. Hope you can get some use out of this.

Get-WmiObject Win32_service -computername $computername | select name, startname | Where-Object{$_.name -like “*SQL*”}