The Multifunctioning DBA


August 14, 2009  8:56 PM

Random Password Script Powershell



Posted by: Colin Smith
Database Administration, Passwords, Powershell, Windows Administration

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.

July 30, 2009  3:17 PM

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



Posted by: Colin Smith
Database Administration, DBA, Groups, Powershell, Roles, SQL Server

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.

 


July 28, 2009  10:18 PM

Migrating SQL 2005 Reporting Services



Posted by: Colin Smith
Database Administration, MSSQL, MSSQL Administration, MSSQL Server, 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/ms156421.aspx


July 28, 2009  7:14 PM

Helping Family



Posted by: Colin Smith
Family, Rant, Remote Support, Support

Yesterday I was not feeling well so I decided to take the day off of work to rest and try to get better. Well things did not really work out that well for me. I am sure that most of you can relate to this and I am curious if you do what I did or if you deal with this in some other manner. Here is the story.

I get a call from my sister-in-law telling me that my mother-in-law has lost internet connectivity to the main computer but all of the laptops in the house that are using wireless are able to connect. No big deal, I figure it is one of a few things. I have her check the simple things first. Make sure the ethernet cable is plugged in at the router and the computer, have her check to make sure that no one jacked up the connection properties and that the computer is not using a static IP, then I have her release and renew the IP. She snips at me telling me that she is not stupid and that she has checked all the cables and that the tech support person at the cable company already had her release and renew and that it did not work. First of all I never called her stupid, and second if you are so smary why are you calling me?, and third, she never told me that she had worked on this with the cable company. I am a bit upset at this point but it is family and I am always willing to help.

Then I have her take one of the working laptops, disable the wireless connection on it and plug it in to the router. I do this to test that the router is working properly and is not the issue. She plugs the laptop in and tells me that she can not get to the internet still. I have her release and renew on the laptop and she tells me that it is not working. OK I say, based on this I would say that we need to get a new router. Once you do that I will help get it all set up.

They go get the router and she calls back, this time telling me she has gone thru what the book says and set it all up but nothing is working now. She is upset that she is worse off now then before. So again, I start with the basics. Lets check the cables first. The modem is plugged into a cable line and then the modem has a ethernet cable going to the router to the internet port.(On this router it is the only Yellow plug) She insists that she has it right. I try to do some more things and nothing is getting IP’s now and that just does not make sense.  I ask again and this time I pull up a picture of the router online so I can see what she see’s. I again walk her thru it and sure enough she has it wrong.

Anyway, long story short, after about 6 hours of this back and fourth we got everything working, we did however find out that it was not the original router but just the nic in the computer that was bad. I had tried to tell her that it could be that instead of the router but she knew best and then acted like I owed her for getting this all resolved. I told her that if she could do it then to leave me alone so I can go to bed but she just laughed and did not understand how mad I really was. So I am curious how you deal with family and if your family depends on you to do all tech support. If so are they so unappreciative of the help that you provide.

Thanks


July 24, 2009  3:47 PM

Another New Language



Posted by: Colin Smith
Database Administration, DBA, Education, Monitoring, Perl, Scripting

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/begperl1.html

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


July 23, 2009  9:43 PM

More with Quest AD Powershell CMDLETS



Posted by: Colin Smith
Active Directory, Domain Administration, Microsoft Windows, Powershell, Windows, Windows Administration

I am continuing work on the script that I am converting from VBScript to Powershell and I must say that it is going quite well with the help of the Quest cmdlets. In the script I want to go through a particular OU and delete any accounts that are currntly disabled, and were created a minimum of 180 days ago, and have not been used in a minimum of 180 days. I can do this with the following block of code.

$deletedays = – 180
$deletedate = [datetime]::Now.AddDays($deletedays)

Get-QADUser -SearchRoot “pni.us.ad.gannett.com/PNI/Users/Disabled” | where{(($_.lastlogontimestamp.value -lt $deletedate) -and ($_.creationdate -lt $deletedate) -and ($_.AccountIsDisabled -eq “True”))}  | Tee-Object -filepath “c:\removedaccounts.txt” | Remove-QADObject -Force

So you will also notice that I am using the Tee-Object cmdlet. This is not a quest cmdlet but it is nice as I can log what accounts I am deleting with the Remove-QADObject cmdlet that is provided by Quest. Be careful when doing things like removing accounts in scripts and be sure to test completly. A good way to test is to use the -whatif clause. This will show you what would happen if you did run it.


July 22, 2009  9:59 PM

Get List of all DC’s in your Domain



Posted by: Colin Smith
Domain Administration, Powershell, Windows Administration

I am working on converting a vbscript that I wrote a couple of years ago into a powershell script. This script requires that I query all of the Domain Controllers in my domain to get the most up to date data that is possible. I used the Quest AD Commanlets and they made it easy. I have discussed these in the past and if you have not gotten them yet then go get them. They are at the following link:

http://www.quest.com/powershell/activeroles-server.aspx

to get a listing of your DC’s just do the following.

$dcs = Get-QADComputer -ComputerRole DomainController

now you have a listing of them in the $dcs variable and you can scan them all.


July 21, 2009  3:29 PM

SQL Server 2008 Learning



Posted by: Colin Smith
Database Administration, Education, MSSQL Administration, MSSQL Server, SQL Server

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

http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1361184,00.html?track=sy41

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


July 17, 2009  8:19 PM

Powershell Add a Login to SQL Server



Posted by: Colin Smith
Database Administration, MSSQL Server, Powershell, 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.


July 17, 2009  3:59 PM

Update sysindexes table in Sybase 12.5.4



Posted by: Colin Smith
Database Administration, DBA, Sybase, Update System Tables

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.jsp?topic=/com.sybase.help.ase_15.0.tables/html/tables/tables34.htm

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.


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: