Lane's Tech Blog

SQL Agent job to query multiple SQL Server Instances

2012-01-23T19:38:00.000-06:00

SSMS provides a nice way to query many SQL Server instances at one time using the Central Management Server (CMS) functionality. You can right-click on a server registration group and query all of the servers in that that group. Very handy. Unfortunately, there isn't anything that simple built in to the SQL Agent engine, so we have to use something else. Here we'll use powershell to run a series of queries against a CMS group of servers to collect a list of databases and save that list in a central location.

The Script
For those of you who are anxious simply to try it out, here's the PowerShell script in its entirety right now:

# Get the list of Instances

$instanceNameList = invoke-Sqlcmd -query "SELECT server_name as Name FROM msdb.dbo.sysmanagement_shared_registered_servers_internal RS

join msdb.dbo.sysmanagement_shared_server_groups_internal SG on

RS.server_group_id = SG.server_group_id where sg.name in ('SQL Server 2008','SQL Server 2005')" -serverinstance cmsserver1

# We're looking for CMS groups with specific names ('SQL Server 2008', etc), so you'll need to

# make sure these match what you have in your environment.

$results = @()   #Initialze the Array

# Populate the array with instance and DB information

foreach($instanceName in $instanceNameList)

{$sqlversion=invoke-sqlcmd -query "exec sp_server_info 2" -ServerInstance $instanceName.Name

$SQLVersion="{0}" -f $sqlversion.attribute_value

$results += Invoke-Sqlcmd -query "select @@servername as instancename, name as dbname,'$sqlversion' as sqlversion from sys.databases  where name not in ('master','model','tempdb','msdb','pubs','northwind')" -ServerInstance $instanceName.Name

}

# Since we're only interested in the latest information, truncate the DBList table

invoke-sqlcmd -query "use dba; truncate table DBList;" -serverinstance cmsserver1

# Write the results of the above query to the DBList table

foreach($db in $results) {

# Since SQL Agent doesn't handle PowerShell variables entirely well, we work around it

# by assigning our variables a little backwards

$instancename="{0}" -f $db.instancename 

$dbname="{0}" -f $db.dbname

$SQLVersion="{0}" -f $db.sqlversion

$querytext="insert into dba.dbo.DBList (instancename,sqlversion,dbname) values ( '$instancename', '$sqlversion','$dbname');"

invoke-sqlcmd -query $querytext -serverinstance cmsserver1 }

Save this as a PowerShell SQL Agent job, and you'll be part-way there. You also will need to make sure that the SQL Agent service account has access to the servers you want to query. Finally, you'll need a table somewhere to store this information

The table DBLIST looks like this referenced in the above query looks like this:

CREATE TABLE DBLIST (

 instancename VARCHAR(50)

 ,dbname VARCHAR(200)

 ,sqlversion VARCHAR(50)

 )

Of course, you can modify this to meet your needs.

Now we'll dissect this a bit, so you can make changes to fit your environment.

Get a list of the SQL Server instances
First we'll query the Central Management Server to get a list of the registered SQL Servers:

$instanceNameList = invoke-Sqlcmd -query "SELECT server_name as Name

 FROM msdb.dbo.sysmanagement_shared_registered_servers_internal RS join msdb.dbo.sysmanagement_shared_server_groups_internal SG

  on RS.server_group_id = SG.server_group_id

    where sg.name in ('SQL Server 2008','SQL Server 2005')" -serverinstance cmsserver1

Here we create an array named instanceNameList that simply contains the names of all of the servers registered in the CMS. Note that these are stored in the MSDB database.

You'll need to change the -serverinstance switch to point to your CMS server.

We're using the invoke-sqlcmd cmdlet to run our queries; our later queries use syntax that isn't available on SQL Server 2000. Because of this, we're limiting our queries to server groups we've defined named "SQL Server 2008" and "SQL Server 2005". You'll need to change these values, as well, to match your environment and needs. Depending on what your environment looks like, you might not even need that where clause at all.

As an aside, note that this is how you can get at the data in the CMS registration: query msdb.dbo.sysmanagement_shared_server_groups_internal.

Query the instances one at a time

Now we'll initialize our results array ($results) and iterate through all of the instance names in our $InstanceNameList array.

Because we want the SQL Server version text (SQL Server 2008, etc.) instead of the version number, we have to do a little more work, utilizing the sp_server_info stored procedure. We first assign that output text to the $sqlversion variable. The sp_server_info SP outputs more columns than we want, however, so in the next line we re-assign the $SQLVersion variable to contain only the data in the "attribute_value" column.
Finally, we run our main query, including the $sqlversion variable as a static selection so that it is a third column in each of the rows.

$results = @()

foreach($instanceName in $instanceNameList)

{$sqlversion=invoke-sqlcmd -query "exec sp_server_info 2" -ServerInstance $instanceName.Name

$SQLVersion="{0}" -f $sqlversion.attribute_value

$results += Invoke-Sqlcmd -query "select @@servername as instancename, name as dbname,'$sqlversion' as sqlversion from sys.databases  where name not in ('master','model','tempdb','msdb','pubs','northwind')" -ServerInstance $instanceName.Name

}

When this is complete, you'll have something like this:

instancename	dbname	SQLversion
SQLSRV1	ProdDB1	Microsoft SQL Server 2008 - 10.0.4064
SQLSRV1	ProdDB2	Microsoft SQL Server 2008 - 10.0.4064
SQLSRV1	ProdDB3	Microsoft SQL Server 2008 - 10.0.4064
SQLSRV2	DBA	Microsoft SQL Server 2008 R2 - 10.50.2500.0
SQLSRV2	TestDB1	Microsoft SQL Server 2008 R2 - 10.50.2500.0
SQLSRV2	TestDB2	Microsoft SQL Server 2008 R2 - 10.50.2500.0
SQLSRV3	CalendarDB	Microsoft SQL Server Yukon - 9.00.5000
SQLSRV4	WebDB1	Microsoft SQL Server Yukon - 9.00.5000
SQLSRV4	WebDB2	Microsoft SQL Server Yukon - 9.00.5000
SQLSRV4	WebDB3	Microsoft SQL Server Yukon - 9.00.5000

Note that sp_server_info returns "Microsoft SQL Server Yukon" for SQL Server 2005 instances. Curious choice, that.

Write the results to the reporting table
Now we'll iterate through all of the rows in the $results array. Normally we'd do something like this to generate our query text:

$querytext="insert into dba.db.dblist (instancename, sqlversion,dbname) 

values ('$($db.instancename)','$($db.sqlversion)','$($db.dbname)');"

Unfortunately, the SQL Agent engine, for whatever reason, sees this syntax as an error, and it won't run, even though it runs fine from a SQLPS command prompt.
Instead, we assign the relevant values to new variables , and it all works OK. Those "{0}" are formatting codes, BTW. You can see a discussion of how that works here.

foreach($db in $results) {

$instancename="{0}" -f $db.instancename

$dbname="{0}" -f $db.dbname

$SQLVersion="{0}" -f $db.sqlversion

$querytext="insert into dba.dbo.DBList (instancename,sqlversion,dbname) values ( '$instancename', '$sqlversion','$dbname');"

invoke-sqlcmd -query $querytext -serverinstance cmsserver1 }

SQL Formatting within SSMS

2011-12-22T09:44:00.002-06:00

I don't think I'm alone in having wished time and again for a good SQL formatting option from within SSMS. I've posted about using Oracle's SQL Developer as a decent SQL formatting tool, but that's a second-rate option, given that it doesn't understand all of TSQL's unique syntax.

I stumbled across a better option yesterday, one that (a) is free and (b) integrates nicely within SSMS.

First, the link: http://architectshack.com/PoorMansTSqlFormatter.ashx: "Poor Man's T-SQL Formatter."
You'll find decent instructions for installing it on the site; the documentation is pretty good.

If you're using SSMS Tools, you'll find that the SSMS Tools find function is mapped to the same default keystrokes as for the PoorSQL formatter. I ended up re-mapping the PoorSQL formatter to use another keystroke combination (CTRL+K, CTRL+Shift+F).

I'm a fan: this integrates nicely into SSMS, it's free, and it works quite well. Check it out.

Estimate recent activity in a SQL Server Database

2011-08-17T18:38:00.013-05:00

It's a common problem SQL Server (especially) DBAs have: how do I find out when the last time this database was used? SQL Server is so prone to sprawl, and so many applications install so many databases; it's hard to keep track of what is in use and what is not. This is especially true when you're new on the job.

Here we'll look at a quick-and-dirty method for getting a guess at whether a database has been used. This comes with a lot of caveats and cautions, but if you're looking for some kind of evidence that a system has been used, index usage stats are one place to look.

A little background is probably in order. Indexes are used to help the database engine find data. They organize (and sometimes order) the data within the database so that when you ask for something, the engine has an idea as to where it might be. That helps speed things up. The database engine keeps track of some information about the indexes in the database: last user seek, scan, and update, among other things. We can use this information to see if any indexes have been utilized in the recent past.

You can see one of the problems you might run into with using this method immediately: what if there aren't any indexes to use? Then it won't work. True, too, if a user is accessing data that isn't indexed. Another problem is that index usage stats aren't persisted across SQL Server reboots, so no usage will show up that was prior to the last time SQL Server was restarted.

So you see, it's not a perfect solution, but it might give you some confirmation of activity (or the lack thereof), instead of just relying on your gut.

So, to the script:

declare @dbname varchar (100)
set @dbname='dbname' -- Change this to match the database you want to check.
select object_name(ius.object_id) as table_name
, ind.name as index_name
, obj.type_desc
, last_user_seek
, last_user_scan
, last_user_update
from sys.dm_db_index_usage_stats ius
join sys.objects obj on ius.object_id=obj.object_id
join sys.indexes ind on ind.index_id=ius.index_id
where database_id in (DB_ID(@dbname))
and obj.is_ms_shipped=0

In short, what we're doing is listing the index usage stats from the dm_db_index_usage_stats DMV. If the last user seek, scan, and update rows are all NULL for the indexes in the database, then those indexes have not been used by a user session. That would suggest, if it's a well-indexed database, that the database has not been used since SQL Server was started.

On the other hand, if those columns do have dates in them, then you know that at least on that date, a user was querying or modifying indexed data, and you have confirmation that it is in use.

Keeping a Minimum Number of SQL Server Backups Online using a SQL Agent Job

2011-04-04T11:34:00.018-05:00

First, a plug: there's a great "maintenance solution" that is a collection of stored procedures available at http://ola.hallengren.com/. This is the basis for a lot of my database maintenance jobs. If you haven’t taken a look at this, I highly recommend that you do; it’s free, under active maintenance, robust, and easy to implement.
When you run the script from that site, you get a variety of stored procedures and you can have it create SQL Agent jobs for you, as well. I’d recommend doing that, as it can give you a good idea of how the stored procedures work.

The backup job

When we perform a backup in a SQL Server instance, we want to perform a number of tasks:

Delete old backups
Backup all of the current databases
Zip up the backup files
Copy the zipped backup file(s) to a share on a backup server

SQL Server (using xp_delete_file) provides a pretty simple method for deleting files that are older than a certain retention window (time-based retention). But time-based retention presents a problem: what if your backups haven’t been running for awhile? The next time your backup job runs, the older backup files (which are all of them) get deleted. Worse: what if there was something wrong with the most recent backup? Suddenly you don’t have any backups online anymore.
I prefer a retention policy based on redundancy: I want to keep at least n copies online at all times, regardless of how old they are. It’s true that a combination of the two policies would be the best-case scenario: keep five days’ worth of backups online, and make sure that we never have fewer than five backup files available at any given time. This would allow us to, for instance, have five backups run in a single day without deleting the older backups that we also want to have available.
But I’ve gone for the simpler route in this case: I want five backup files online at all times. SQL Server doesn’t give us an easy built-in way to do this, so we’ll turn to PowerShell for our process.

Delete old backups

Our first step in the job is to delete the backups that aren't used anymore. In subsequent steps, we have turned off the archive bit on the files we do not need anymore, so we simply delete the files that don't have the archive bit set. The PowerShell script is below:

$backup_dir="path:\to\Backup\dir" 

$files=get-childitem -path $backup_dir 

# we'll delete all files that don't have the archive bit set 

Foreach($file in $files)

  {  If((Get-ItemProperty -Path $file.fullname).attributes -band [io.fileattributes]::archive)     

     { Write-output "$file is set to be retained" }

     ELSE {

     Write-output "$file does not have the archive bit set.  Deleting."

     remove-item -recurse $file.fullname

     $output =$_.ErrorDetails } } 

#end Foreach

Note that the $BACKUP_DIR variable needs to be set to the correct directory for the backups.
Any file or directory in the backup directory that does not have the archive bit set will be removed. Do pay attention to this fact. You can put a file mask in the get-childitem cmdlet call to modify that behavior, if you choose.

Run DatabaseBackup

DatabaseBackup is the name of the stored procedure (in the master DB) that backs up each of the databases on the instance. It is installed as a part of the maintenance solution referenced at the beginning of the page. Usage is as follows:

EXECUTE [dbo].[DatabaseBackup]

     @Databases = 'USER_DATABASES',

     @Directory = @backup_dir,

     @BackupType = 'FULL',

     @Verify = 'Y',

     @CleanupTime = 24,

     @CheckSum = 'Y'

@Databases can be one of:

'USER_DATABASES' backs up all user databases
'SYSTEM_DATABASES' backs up all system databases (master, model, msdb)

@BackupType can be one of

'FULL' performs a full backup of the database data files
'LOG' backs up the transaction log files
'DIFF' creates a differential backup from the last full backup

So here’s what our next step looks like. It’s a T-SQL step:

-- change the backup directory/drive appropriately 

declare @backup_dir varchar(100) ='path:\to\backup\dir'  

EXECUTE [dbo].[DatabaseBackup]

       @Databases = 'SYSTEM_DATABASES',

       @Directory = @backup_dir,

       @BackupType = 'FULL',

       @Verify = 'Y'  

EXECUTE [dbo].[DatabaseBackup]

        @Databases = 'USER_DATABASES',

        @Directory = @backup_dir,

        @BackupType = 'FULL',

        @Verify = 'Y'  

EXECUTE [dbo].[DatabaseBackup]

        @Databases = 'USER_DATABASES',

        @Directory = @backup_dir,

        @BackupType = 'LOG',

        @Verify = 'Y'

The verify switch is quite nice: after each backup, it runs a 'RESTORE VERIFYONLY FROM DISK=..." to ensure that each file is recoverable.
Basically, we’re backing up all of the system databases, all of the user databases, and then the t-log files from all of the user databases, and we’re saving those backups to a directory structure at the @backup_dir variable location we specified at the beginning.
Note that this stored procedure puts its files in directory structure starting in the @backup_dir. The start of this structure is the server name, with directories under it for each database.

Zip the backup files

We don’t want to keep the uncompressed backups online all the time, so we’ll compress them using 7-Zip.
This PowerShell script is more complicated, so we'll go through it in some more detail. Note that this needs the 7zip executable (and associated .dll). This script will look for it in the c:\utils directory. Note that you can copy just the 7z.exe and 7z.dll to a directory; you don’t have to install the entire package in order to use the 7-Zip command line.
First, here's the whole of our PowerShell script:

$backup_dir="path:\to\backup\dir"  

$day= get-date -format "yyyyMMdd_HHmm"  

# Turn on the archive bit on the current backups directory 

# (so it won't get deleted at the next run if the zip process fails) 

attrib $backup_dir\$env:computername +a  

# Zip up the current backup(s) 

# destination for the zip file is $backup_dir\SQLBACKUP-<servername>-DATE_TIME.zip 

C:\utils\7z.exe -tzip -mx1 a $backup_dir\SQLBACKUP-$env:computername-$day.zip $backup_dir\$env:computername  

# if 7zip succeeded, we'll continue 

if ($LASTEXITCODE -gt 0)

     {Throw "7Zip failed" } 

ELSE {

     # When the zip is complete, turn off the archive bit on the current backup directory

     attrib $backup_dir\$env:computername -a  

# Now let's change the archive bit, such that only 

# the last five zipped backups will be kept online  

$delfiles=0 

$delfiles= (dir $backup_dir\SQLBACKUP*.zip).count-5  

if ($delfiles -gt 0)   

#  If there are more than 5 zipped backups, we'll turn off the archive bit on them

     {dir $backup_dir\SQLBACKUP* | sort-object -property {$_.CreationTime} |

     select-object -first $delfiles |

     foreach-object { attrib  $_.FULLNAME -A} }}

So. The first line sets the backup directory to use. Next, we set a variable to hold today's date and time to use in creating the zip file.
Next we turn on the archive bit for the directory created during the previous step, and then we run 7z.exe to create the zip file. All pretty straightforward up to this point, though do note that we’re using the –mx1 switch in 7Zip. This is important because 7Zip is optimized for compression, not for speed. Using the –mx1 switch tells 7zip to use its fastest (and least CPU-intensive) compression routines. Especially for large files, this is really important.
Our next step is to check to make sure that 7zip succeeded. We do that with the $LASTEXITCODE variable:

if ($LASTEXITCODE -gt 0) {Throw "7Zip failed" }

This says, if 7Zip failed (returning an error code that is greater than zero), end (throw) with failure text "7Zip failed".
If the exit code is zero, then we know 7Zip succeeded, and we'll continue.
The next step is to turn off the archive bit on the directory we just zipped up; that way it'll be deleted when the job runs next.
We also want to keep the five most recent backups online on the server. We don't want to just delete files that are older than five days, though: if the backup was failing, and there aren't backups from days 1-4, we'd suddenly have lost all of our backups. So we loop through the files and sort them by date. Then, if there are more than five files in the directory, we take the oldest files #6 - n and turn off the archive bit on them. That way, those files will be deleted the next time the job runs.
This is the code that does this (thanks, BTW, to Spiceworks for the example script on which this is based.):

$delfiles=0 $delfiles= (dir $backup_dir\SQLBACKUP*.zip).count-5

  if ($delfiles -gt 0)   #  If there are more than 5 zipped backups, we'll turn off the archive bit on them

  {dir $backup_dir\SQLBACKUP* | sort-object -property {$_.CreationTime} |

     select-object -first $delfiles |

     foreach-object { attrib  $_.FULLNAME -A} }

What this does is the following:

Count the number of .zip files in the backup directory
If the number of files in the backup directory is > 5, then:
Sort the directory (SQLBACKUP*) by creation time (oldest first)
Take the first n files in the sorted list (where n is the number of files that are greater than 5) and turn off the archive attribute on them.

Copy files to the backup server

Finally we'll copy the files to the backup server:

# Make sure you change the backup directory appropriately 

$backup_dir= "path:\to\backup\dir"  

$day= get-date -format "yyyyMMdd_"  

# This will copy all of today's backups to the backup server  

copy-item $backup_dir\SQLBACKUP-$env:computername-$day*.zip \\server\sharename -force

Note that the SQL Agent service account needs to have access to the share in order for this to succeed. Note, too, that this job will copy all files from today, so if there were multiple runs today, all of those files will get copied again (overwritten; that’s the need for the –force switch).
So now we have a backup job that will keep the backups around not based on age but on the number of copies. When you put the scripts above together in a job, the steps look something like this:

Formatting SQL Server TSQL with Oracle's SQL Developer

2011-03-02T11:01:00.005-06:00

If you do much troubleshooting on Microsoft SQL Server, you inevitably will end up having to deal with a poorly-formatted (and hard-to-read) SQL statement from a query using sys.dm_exec_sql_text or the like.

There are lots of online formatters out there, though I've had decidedly mixed results with them. There also are a lot of add-in and standalone products available that will do a good at this. Here's another one to add to your list, until SSMS includes a formatting feature: Oracle's SQL Developer.

SQL Developer is a free download (here) that will, in fact, connect to SQL Server instances. While I do not use it for my day-to-day SQL Server administration tasks, I use it regularly to reformat SQL that I've pulled from the DMVs.

Here's how it looks using the following SQL Agent task SQL.

(@P1 int,@P2 uniqueidentifier,@P3 int)UPDATE msdb.dbo.sysjobactivity SET run_requested_date = DATEADD(ms, -DATEPART(ms, GetDate()),  GetDate()), run_requested_source = CONVERT(sysname, @P1), queued_date = NULL, start_execution_date = NULL, last_executed_step_id = NULL, last_executed_step_date = NULL, stop_execution_date = NULL, job_history_id = NULL, next_scheduled_run_date = NULL WHERE job_id = @P2 and session_id = @P3

First we paste this in to a new page in SQL Developer:

And then we hit CTRL-F7 (or right-click and select "Format"):

Which gives us very nicely formatted SQL. One gotcha here: SQL Developer doesn't know what to do with the 'GO' statement, so it puts it on the same line as other SQL commands. This will keep your code from running, so there's one piece of cleanup that is necessary when using SQL Developer.

How to Change the Owner of All SQL Agent Jobs in a SQL Server Instance

2011-02-25T10:56:00.004-06:00

Each job in a SQL Server instance has an owner, and you may run into a situation in which that owner needs to be changed.
If there are a lot of jobs that were created by that owner, this can be a tedious task.
Here we’re opening a cursor and looping through the SQL Agent Jobs in the instance that are owned by the old user (@olduser) and executing the sp_update_job stored procedure to change that to match @newuser.

USE MSDB

GO

declare @jobname varchar (200)      

declare @oldusername varchar (30) 

declare @newusername varchar(30) 

set @oldusername=’DOMAIN\oldusername’      

set @newusername=’DOMAIN\newusername’ 

declare cur_jobname cursor LOCAL

for select name from sysjobs

where suser_sname(sysjobs.owner_sid) =@oldusername

open cur_jobname

fetch next from cur_jobname

into @jobname

While @@FETCH_STATUS = 0

begin

EXEC msdb.dbo.sp_update_job @job_name=@jobname,

@owner_login_name=@newusername

fetch next from cur_jobname

into @jobname

end

close cur_jobname

deallocate cur_jobname

How to Change the Owner of All Databases in a SQL Server Instance

2011-02-25T10:23:00.005-06:00

Each database in a SQL Server instance has an owner, and you may run into a situation in which that owner needs to be changed. One example of this would be a case when a DBA moves departments, but stays in the organization. In that case, the account would still be active, but you’d probably want to change the database owner.
If there are a lot of databases that were created by that owner, this can be a tedious task.
Here, we’re opening a cursor and looping through the databases in the instance that are owned by the old user (@olduser) and executing the sp_changedbowner stored procedure to change that to match @newuser.

USE MASTER 

GO 

declare @dbname varchar (50) 

declare @oldowner varchar (30) 

declare @newowner varchar (30) 

declare @sql varchar (300) 

set @oldowner='DOMAIN\oldusername' 

set @newowner='DOMAIN\newusername' 

SET @sql='' 

declare cur_dbname cursor LOCAL

  for SELECT name

    FROM master.sys.databases where SUSER_SNAME(owner_sid)=@oldowner

  open cur_dbname

  fetch next from cur_dbname

    into @dbname

  While @@FETCH_STATUS = 0

    begin

      set @sql='exec ['+@dbname+'].sys.sp_changedbowner ''' + @newowner + ''''

      --    PRINT @sql

      EXEC (@sql)

      fetch next from cur_dbname

      into @dbname

    end 

 close cur_dbname

 deallocate cur_dbname

AD Authentication with RHEL 6

2010-11-29T16:49:00.007-06:00

We’ve been using AD authentication with our RHEL and CENTOS 4 and 5 systems for some time, now, so I was anxious to see what kinds of changes might have come up with RHEL6. Not much, happily, but there was one change that took a little while to figure out. We’ll run through all the steps, from beginning to end, here.

Install the prerequisites

We’re using samba and samba-winbind for this, so make sure these are installed.

yum install samba samba-winbind

If you’re running RHEL5 and a Windows 2008 R2 domain, you’ll want to use samba3x, instead of the samba. See this article for more information on that front.

Edit the Configuration Files

you’ll want to have the following settings. I’ve grouped them here to make it all more readable. Changes from the default are in blue.

/etc/krb5.conf

default_realm = EXAMPLE.COM 

dns_lookup_realm = true 

dns_lookup_kdc = true 

ticket_lifetime = 24h 

renew_lifetime = 7d 

forwardable = yes  

[realms] 

EXAMPLE.COM = { 

default_domain = example.com 

} 

[domain_realm] 

.example.com = EXAMPLE.COM 

example.com = EXAMPLE.COM

Here we’re defining the kerberos realm and domains. “example.com” will be replaced with your AD domain name. Do note the capitalization; it matters.

/etc/samba/smb.conf

workgroup = example 

realm = EXAMPLE.COM 

security = ads 

idmap uid = 10000-500000 

idmap gid = 10000-500000 

template shell = /bin/bash 

winbind use default domain = true 

winbind offline logon = false 

winbind nested groups = yes 

encrypt passwords = yes

Here we’ve told samba to use the kerberos realm EXAMPLE.COM (you’ll substitute your domain from the krb5.conf file). We’re using ads for security (Windows-style), and we’re allocating a bunch of UIDs and GIDs for mapping the domain users and groups to the Linux equivalents.

/etc/nsswitch.conf

passwd:     files winbind 

shadow:     files 

group:      files winbind

Here we’re telling the system to look not only in the /etc/passwd and /etc/group files for authentication, but also to use winbind.

Join the Domain

Now’s the fun part: we can join the system to the domain.

chkconfig smb on 

service smb restart 

net ads join –U username

where username is a domain user who has permissions to join a computer to the domain. You should get a response that the server has joined your realm.
Depending on your DNS configuration, you might get some errors like the following:

[root@linuxserver1]# net ads join -U username 

Enter username's password:  

Using short domain name – EXAMPLE 

Joined 'LINUXSERVER1' to realm 'example.com'  

[2010/11/29 16:11:20.643445,  0] libads/kerberos.c:333(ads_kinit_password) kerberos_kinit_password LINUXSERVER1$@EXAMPLE.COM failed: Client not found in Kerberos database 

[2010/11/29 16:11:20.644894,  0] utils/net_ads.c:1147(net_update_dns_internal) net_update_dns_internal: Failed to connect to our DC! DNS update failed!

So long as your server created a machine account in the domain, you can ignore the above errors. It’s trying to update your DNS server, and if you’re not using a Microsoft DNS server as a part of your domain, it will fail. That’s OK.
Once you’ve joined the domain, we need to start winbind

chkconfig winbind on 

service winbind start

Assuming winbind starts without any errors, you can test your membership and domain communication with the wbinfo command:

wbinfo –g

This command should list out all of the groups you’ve got configured in your domain.

Configure the home directories

We’ll first want to edit the /etc/oddjobd.conf.d/oddjobd-mkhomedir.conf file; this defaults to creating home directories that are group- and world-readable. We don’t want that. This, BTW, is a change from what we did in RHEL 5 and below.
There are two lines in the /etc/oddjobd.conf.d/oddjobd-mkhomedir.conf file that look like this:

<helper exec="/usr/libexec/oddjob/mkhomedir -u 0022"

Change them to read:

<helper exec="/usr/libexec/oddjob/mkhomedir -u 0077"

Then restart the oddjobd service

service oddjobd restart

Set the domain home permissions

*Note: the behavior below is reported as fixed in advisory RHBA-2011:0339-2, but I'm leaving the information here for posterity's sake.
There's a curiousity with the way oddjobd works, here: it seems to be assuming that the default umask is 0022 (644 permissions equivalent). When we change the umask 0077 (700), it not only creates the users' home directories with these permissions, but *also the domain home*. As it turns out, the domain home (/home/DOMAIN) is owned by root. This prevents anyone from being able to get to their home directory.
Unless we change this, users will receive the following error:

Could not chdir to home directory /home/DOMAIN/user: Permission denied 

-bash: /home/DOMAIN/user/.bash_profile: Permission denied

But if we pre-create the domain home, we should be in good shape:

mkdir /home/DOMAIN 

chmod 711 /home/DOMAIN

Where DOMAIN is the short name of your AD domain.
There's a bug for this behavior, BTW, at https://bugzilla.redhat.com/show_bug.cgi?id=666418, if you're interested in such things.
This should set you up so that everyone can change directories to this, but no one can read or write to this directory. If you prefer users to be able to enumerate the contents of your DOMAIN directory, change the permissions to 755 instead of 711.

Restrict Logins

We’ll first edit the /etc/pam.d/password-auth file (you can have the OS do this with a GUI front-end for you, if you run authconfig-gtk). This tells pam to use, in addition to the local user store, winbind for authentication.

Domain authentication isn’t that useful unless you can use it to control who can and cannot log in to your server.

#%PAM-1.0 

# This file is auto-generated. 

# User changes will be destroyed the next time authconfig is run. 

auth        required      pam_env.so 

auth        sufficient    pam_unix.so nullok try_first_pass 

auth        requisite     pam_succeed_if.so uid >= 500 quiet 

auth        sufficient    pam_winbind.so use_first_pass 

auth        required      pam_deny.so 

account     required      pam_unix.so broken_shadow 

account     sufficient    pam_localuser.so 

account     sufficient    pam_succeed_if.so uid < 500 quiet 

account     [default=bad success=ok user_unknown=ignore] pam_winbind.so 

account     required      pam_permit.so 

password    requisite     pam_cracklib.so try_first_pass retry=3 type= 

password    sufficient    pam_unix.so sha512 shadow nullok try_first_pass use_authtok 

password    sufficient    pam_winbind.so use_authtok 

password    required      pam_deny.so 

session     optional      pam_keyinit.so revoke 

session     required      pam_limits.so 

session     optional      pam_oddjob_mkhomedir.so 

session     [success=1 default=ignore] pam_succeed_if.so service in crond quiet use_uid 

session     required      pam_unix.so

The mkhomedir line has PAM create a home directory (defaulting to /home/DOMAIN/username). That’s usually a good idea.
It’s possible to put login restrictions here in the system-auth file, but it’s usually considered best practices to put those in the individual files for the connection methods. We’ll do that now.

/etc/pam.d/sshd

#%PAM-1.0 

auth       required     pam_sepermit.so 

auth       include      password-auth 

auth       include      system-auth 

account    required     pam_nologin.so 

account    include      system-auth 

account    sufficient   pam_localuser.so 

account    required    pam_succeed_if.so user ingroup DOMAIN\linuxadmins 

password   include      system-auth 

# pam_selinux.so close should be the first session rule 

session    required     pam_selinux.so close 

session    required     pam_loginuid.so 

# pam_selinux.so open should only be followed by sessions to be executed in the user context 

session    required     pam_selinux.so open env_params 

session    optional     pam_keyinit.so force revoke 

session    include      password-auth

Here we’re telling pam that only users who are in the group linuxadmins are allowed to connect to our server though ssh.

/etc/pam.d/gdm

#%PAM-1.0 

auth     [success=done ignore=ignore default=bad] pam_selinux_permit.so 

auth       required    pam_succeed_if.so user != root quiet 

auth       required    pam_env.so 

auth       substack    system-auth 

auth       optional    pam_gnome_keyring.so 

account    required    pam_nologin.so 

account    include     system-auth 

account    sufficient  pam_succeed_if.so user = DOMAIN\user34 

account    sufficient  pam_succeed_if.so user ingroup DOMAIN\linuxadmins 

account    required    pam_succeed_if.so user ingroup DOMAIN\domainadmins 

password   include     system-auth 

session    required    pam_selinux.so close 

session    required    pam_loginuid.so 

session    optional    pam_console.so 

session    required    pam_selinux.so open 

session    optional    pam_keyinit.so force revoke 

session    required    pam_namespace.so 

session    optional    pam_gnome_keyring.so auto_start 

session    include     system-auth

We’re giving more people access to the GUI login, though. Notice that we’re allowing user34, linuxadmins, and domainadmins to log in through GDM.

Using Error (and other multiple) Paths in SSIS

2010-11-27T23:04:00.004-06:00

SSIS provides for multiple paths between tasks. Very helpful stuff; here we’ll look at a simple solution to a common scenario.
Below is a job that stops a blocking service, reads from the source DB (SQL CE) file, truncates the destination table, and then copies the data from the source to destination. Finally, it starts the service again.

What if, however, our test job fails after stopping the service? We can provide for failure notification through SQL Agent jobs, but wouldn’t it be nice, also, to have the service start again, even after a failure?
We’ll set that up using failure paths, such that the tasks will go directly to the start service task in the event of a failure.

First, we want to highlight the rest read task. When we do, we have the option of creating another job path (highlighted below).

We’ll drag this new path to the start service task:

Now we’ve got two paths from the test read task, but they’re both success paths. To change that, double-click on the green arrow between the test read and the start service paths. When you do, you’ll see a window like the one below, showing the defaults:

We need to change a couple of things here. First, we want this to be a path for failures, so change the success value field to “failure.”
Next, we want to change this to an OR operation. SSIS allows you to say, “only proceed to this next step if all of the conditions pointing to it are true.” That is, you can have multiple branches in a job that converge on a single task, and that task will not fire until all of the previous tasks have completed successfully (or not; your choice). So it’s really powerful stuff. In our case, we’re going for simple: we will only have a single path to the final task in any given execution, which is the definition of an OR.

Now, once we click on OK, we’ll notice a couple of things. First, the path from the test read task is red and dotted. Second, the green path from our copy task is also dotted. This is how SSIS signifies that these are OR operations. Note that the constraint setting belongs to the target task, not to the paths, themselves.

We need to do one more thing to keep SSIS from stopping the package execution when this task fails. Right-click on the task and select properties. We want to change the “FailPackageOnFailure” field from True (default) to false. If it’s set to true, processing stops when the task fails. We do, however, want to make sure the “failParentOnFailure” is set to true.

So now we’re handling a source read failure more gracefully. What about a failure on the destination, though? We probably ought to handle those, as well, right? So we’ll do the same process with the truncate task. When we’ve done that, our task looks like this:

So far so good. What about that copy task, though? It’d sure be nice to handle failures on that one. We can’t, though, have two paths going between those last two steps. Happily, we have more than just success and failure as options. Double-click on the arrow between the last two tasks, and change the constraint option from “success” to “completion.”

Now, that arrow is colored blue, to indicate that it will proceed to the next step regardless of whether the previous step succeeded or failed.

And now, without much work or time, we have completed a project that would take a lot of work to do with traditional server scripting tools, and we’ve done it all within a single SSIS project.

Creating a SSIS SQL Compact Data Source

2010-11-27T14:10:00.011-06:00

There’s no out-of-the-box SQL Compact data source in SSIS, which presents a problem when you’re needing to copy data from a SQLCE data file.

It turns out, though, that it’s easy to repurpose a OLEDB connection to read from a SQL Compact DB.

OLEDB is a generic connection method: so long as you know the correct connection string to use, you can manually edit the connection properties for another OLEDB connection.

Start out by creating a new OLE DB Connection in the connection manager. Right-click in the connection managers pane and select “New OLE DB Connection…”

Click on the “New…” button within the connection manager configuration window.
In the Connection Manager provider field, select “Native OLE DB\Microsoft OLE DB Simple Provider.” In the “Server or file name” field, enter a dummy file name. We’ll change this to point to the correct file in a moment.

Click on OK.
Now you’ll see your dummy OLE DB connection manager in the connection managers window. If you right click on it and select Properties, you’ll see the information below:

Now we can change the connection string manually.
The format for a SQL Compact connection string is as follows:

Data Source=path\to\filename.ext;Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;File Mode=Read Only;

The FileMode setting is optional, and I’ve not been able to have it make a difference when opening a SQL Compact file from within SSIS.
You’ll also want to change the name of the connection to something that is more descriptive.
Once you’ve changed the connection string (example below; sorry for the blurry image; click on it for a clearer view), you’re able to use this connection as a data source through the OLE DB Source toolbox.

Note that once you’ve made this change, you’ll no longer be able to double-click on the data connection manager to modify its settings. You’ll receive an error like this:

This isn’t an error to worry about: the problem is that the GUI isn’t set up to handle SQL Compact databases as an OLE DB connection. But it still will work within your data flow, both as a source and as a destination.

Connection Failed error message with PeopleTools Change Assistant on 64 Bit Windows

2010-09-22T16:53:00.005-05:00

With PeopleTools 8.5, Oracle moved the app servers into 64-bit territory. That was welcome news. Unfortunately, the whole stack isn't quite there, yet, and we ran into an irritating problem when running Change Assistant to upgrade from 8.49 to 8.51. Specifically, when setting up the environment, we recevied a "connection failed!" error when testing the connection. Here's what we saw: No logs, no details. Just failed. But then, finally, a hint: Data mover (which shouldn't connect, since the database is still at 8.49) wouldn't even run: it failed with an error "missing or invalid version of sql library psora". Aha! Now that is something one can work with. It turns out it needs the 32-bit Oracle client. Install that, and everything is good, again.

How to Delegate Services control in Windows

2010-07-16T12:52:00.008-05:00

Microsoft offers a very helpful document here and here detailing how to use subinacl to give control over a service to a user. Unfortunately, they’ve not updated that article in quite some time, and it’s now out of date: beginning with Windows Server 2003 SP1, authenticated users no longer can enumerate services.
While that’s a good thing, it renders the solution presented by Microsoft only partially complete.
So we’ll correct that, going through all the steps that are necessary to give an (otherwise) unprivileged user permissions to control any given services through the services control panel. This will work on Windows Server through v2008 R2.

Assemble the Tools

We’ll use three command-line tools, two of which will need to be installed on the destination server. The first is sc, which is installed by default, happily.
The second is subinacl. It’s possible to use sc instead of subinacl, but the learning curve for subinacl is much less steep. Subinacl can be downloaded from Microsoft here.
You’ll also need access to dsquery; this is installed on Windows 7 and Server 2008 if you’ve installed the Active Directory Domain Controller Tools feature (see below for a screen shot on how to install that feature in Windows Server 2008).

Grant Permissions to Enumerate Services

So with Windows Server 2003 SP1, authenticated users no longer can enumerate services by default. Probably a good choice, but that means we need to grant that permission before our delegate can run the services control panel and connect it to our server.

Determine the SID of the user you’re wanting to grant access to

Because SC uses SIDs, rather than usernames, we’ve got to get the SID of the user we want to deal with. We’ll use dsquery to do this, thus:

dsquery * -filter "&(objectcategory=user)(samaccountname=<username>)" -attr objectsid

where <username> is the login name for the user we want. The above will return the SID, something like this:

C:\>dsquery * -filter "&(objectcategory=user)(samaccountname=testuser)" -attr objectsid


objectsid
S-1-5-21-214A909598-1293495619-13Z157935-75714

Note the SID; we’ll use it below.

Grant access to run the Services Control Panel

We’ll use sc to do this. First, run sc to get the current SDDL for the services control manager:
sc sdshow scmanager
You’ll get something like this:

D:(A;;CC;;;AU)(A;;CCLCRPRC;;;IU)(A;;CCLCRPRC;;;SU)(A;;CCLCRPWPRC;;;SY)(A;;KA;;;BA)S:(AU;FA;KA;;;WD)(AU;OIIOFA;GA;;;WD)

This is SDDL, and if you’re interested, you can start reading with Microsoft’s KB914392 for more information.
For our purposes, though, it’s enough to do this:

Copy the results of the above command (sc sdshow scmanager) to a text editor.
Copy the section of the SDDL that ends in IU (interactive users) to just before the S: in the SDDL line.
In the copied text, replace ‘IU’ with the SID of the user to whom you are granting access, such that your SDDL looks something like that below:

D:(A;;CC;;;AU)(A;;CCLCRPRC;;;IU)(A;;CCLCRPRC;;;SU)(A;;CCLCRPWPRC;;;SY)(A;;KA;;;BA)(A;;CCLCRPRC;;;S-1-5-21-214A909598-1293495619-13Z157935-75714)S:(AU;FA;KA;;;WD)(AU;OIIOFA;GA;;;WD)

Now we’ll run the set command:

sc sdset scmanager "D:(A;;CC;;;AU)(A;;CCLCRPRC;;;IU)(A;;CCLCRPRC;;;SU)(A;;CCLCRPWPRC;;;SY)(A;;KA;;;BA)(A;;CCLCRPRC;;;S-1-5-21-214A909598-1293495619-13Z157935-75714)S:(AU;FA;KA;;;WD)(AU;OIIOFA;GA;;;WD)"

Note that we’re replacing the permissions on scmanager; this isn’t additive. That’s why we needed to copy the existing permissions. So if you’re needing to grant access to another person, you’ll need to duplicate the section with the SID and add another.

Grant access to the Service

Now that our user can enumerate services, we’ll grant access to control a service. In our example here, we’ll use the Print Spooler service, since that’s a harmless one, but you can do this with any service(s) you require.
This is where we’ll use subinacl, and the format is simple:

subinacl /verbose /service "service name" /grant=DOMAIN\username=F

Note that the service name is the “short” format. You can query that using sc:

sc getkeyname “service name from services control panel”

thus:

C:\>sc getkeyname "print spooler"
[SC] GetServiceKeyName SUCCESS
Name = Spooler

So in our case, we’ll do this:

subinacl /verbose /service “Spooler” /grant=TEST\testuser=F

‘F’, by the way, is full control.
The double quotes aren’t necessary, except in cases where the service name has spaces.
Our user now can run the services control panel on a remote workstation and connect to the server to control the print spooler service.
Note that even though the user can see other services, there’s isn’t any permission to control them.

Linux error id: cannot find name for user ID xxxxx when using Domain Authentication

2010-06-27T16:42:00.005-05:00

We recently had a problem, after re-doing some samba configurations on RHEL 5, in which a user would log in (successfully), but then be presented with the follow errors:

id: cannot find name for user ID 10001

id: cannot find name for group ID  10000

id: cannot find name for user ID 10001

Of course, none of our domain ACLs worked for this user, either, which was a real problem. Finally, after running through the more obvious problems (communication with domain controllers: verified with wbinfo; uid and gid allocation and linking: set explicitly with wbinfo; winbind cache (cleared, both in /var/cache/samba and /var/lib/samba); date/time discrepancies; domain membership), we found the culprit: file permissions.
One of the perplexing things about this problem was that it was user-specific: all other users could log in just fine, and in fact, this user could be identified on the domain by other users. If you run into this problem, there are a couple of really useful troubleshooting commands:

Id gives information about users and groups. In this case, other users could get information about this user using the id command, but the user couldn't get any data either about his own ID or anyone else's.

strace

Ah, how I love strace. I often forget to use it, which is a shame, because it would short-circuit a lot of my problems. (man strace for more info; you'll be glad you did.) In this case, the strace stack for id when run as the offending user included the following:

connect(3,  {sa_family=AF_FILE, path="/var/run/nscd/socket"...}, 110) = -1 EACCES  (Permission denied)

Our GID mapping had gotten out of whack, and the /var directory, as it turned out, had some extended permissions (ACLs) that excluded this particular user from accessing anything within the /var directory. Removing those extended permissions cleared up the problem for us.

NT_STATUS_PIPE_DISCONNECTED with Samba Winbind and Windows Server 2008 R2 Domain Controller

2010-06-21T12:26:00.005-05:00

We recently upgraded our domain controllers to Windows Server 2008 R2, and our RHEL 5 authentication through our Windows domain immediately broke.
Here was the error:

[2010/06/21 09:32:57, 0] rpc_client/cli_pipe.c:rpc_api_pipe(790)
rpc_api_pipe: Remote machine adserver.my.edu pipe \NETLOGON fnum 0x8007returned critical error. Error was NT_STATUS_PIPE_DISCONNECTED

A little searching online shows a lot of people with this or related problems, but the solutions appear to be many, and there mostly isn’t a solution posited.
But there is this: https://bugzilla.redhat.com/show_bug.cgi?id=561325
In short: there's a bug in the samba package that prevents it from working with Windows Server 2008 R2 domains. If you’re running into this problem, the solution is to remove your existing samba installation and install, instead, the samba3x packages.
Note that samba3x was a "technology preview" from RedHat, which means that it offered little support for it. This has changed, and it's now a supported package in RHEL 5.

yum erase samba samba-common

yum install samba3x samba3x-client

You’ll have to re-do your configuration, so it might be worthwhile to back up your /etc/samba/smb.conf file.

Simple Method to Validate Data Read at the beginning of an SSIS Package

2010-04-25T22:26:00.003-05:00

We’ve looked at using transactions in an SSIS package to ensure that, for instance, the read data step in your package doesn’t fail after you’ve deleted the data it’s set to replace.
This is a very effective and really useful method, and it’s exceedingly flexible.
If your project spans multiple servers, though, this will require changes to the DST service settings that you might not be able to make.
There’s a simpler way, though it doesn’t offer all of the protections of wrapping your package in a transaction. We’ll take a look at that, here.

First: the problem

The stereotypical SSIS package looks like the one above: delete data in the destination database, then import new data into the target. The problem is also displayed above:
What if you are successful in deleting the data, but then there’s a failure, either in reading the new data? This failure could be from a password change, permissions problems on the source, or, in the case of a flat file source, a nonexistent file.
In any of those scenarios, we’re faced with data that already has been deleted, but there’s nothing to replace it with.

Solutions

There are a couple of possible solutions to this problem, including using transactions or building in a failure step that would copy the data back from your backup. The first option, utilizing transactions, is certainly the most robust option. But it often requires some server-side settings that not every DBA or data analyst has access to change.
The second option, building in some failure events in your job, is easy enough, but it can complicate what is an otherwise very straightforward process.
A third option is simply to test your read in a task that executes before your main task. This is the option we’ll use here, as it’s very simple, both conceptually and in implementation. Plus, it meets our basic requirements of providing a failure before data is deleted.

The details

Here’s a data flow task that we use daily. We read from the source, do some data transformations, and then write the data to a destination table. That table has already been truncated in an earlier task.

Because the truncate already has happened, if there’s an error at the source read step here, we’re in trouble.
So we create a new data flow task before our delete step. That data flow task consists of one thing only: the read from source step from the above task. Just copy and past.
We don’t actually do anything with this data, and, in fact, were it a really large data read, we could do something like retrieve the top 10,000 rows, just to be sure that all is well on our source.

As you can see, if that read step fails, now, the package fails, and no data has been lost.
While we’ve not provided bulletproof failure protection, we’ve eliminated the most likely failure scenario, and for a lot of folks, that’s a really big improvement.

Utilizing Transactions in SSIS to Rollback After a Failed Import

2010-04-23T16:41:00.012-05:00

SSIS is used primarily to import data into a database, particularly from flat files, but also from other databases. The typical SSIS package for doing this task looks something like the picture at the left: backup the current data, delete the current data from the destination table, and then import the data from the source.
This is all good, and it works well. When it works. The problem is that if there’s a problem with reading the data from the source (say, the credentials for the source database changed), you’ve already blown away the current (production) data. The job fails, and the data remains missing.

Configure MSDTC

If you’re running the SSIS package on the destination database server, you can skip this step. We’ve chosen to have SSIS on a dedicated instance, which has made keeping track of packages and scheduled jobs much easier. But it also means we’ve got to do a little work on the back-end before we can make our packages use a single transaction
The MSDTC service (Distributed Transaction Coordinator) facilitates this process, and by default, it doesn’t allow remote communication. So we’ve got to change the settings on two systems: the database server and the server on which the job is executed. In our example, we’ll say that the db server runs Windows Server 2003 and the Server 2008; setting up the MSDTC service under each OS is just a tiny bit different.

Configure MSDTC in Windows Server 2003

Run the component service control panel: Start -> Administrative Tools -> Component services (or Start -> run -> dcomcnfg)
Go to Component Services -> Computers and right click on My Computer. Select Properties.

The properties window has a MSDTC tab. Click on the Security Configuration button in the MSDTC tab.
In the security configuration window (below), you'll need to grant Network DTC Access, allowing remote clients, both inbound and outbound. You do not need to allow remote administration, though doing so would allow these settings to be (re)configured remotely.
The settings below are what are required for a database server.
Basically, we want our SSIS server to be able to communicate to the MSDTC service on our database server, and we want our service to be able to talk back to the SSIS box.
When you click on <OK>, the system will restart the Distributed Transaction Coordinator service.

Configure MSDTC in Windows Server 2008

The actual configuration settings and windows are identical in Windows Server 2008 to those in Windows Server 2003. There is a difference, though, in how you get to those settings:
Run the component service control panel: Start -> Administrative Tools -> Component services (or Start -> run -> dcomcnfg)
Go to Component Services -> Computers -> My Computer -> Distributed Transaction Coordinator
Right-click on on "Local DTC" and select properties. There is a Security tab on that window. The settings below are required on the server that is executing the SSIS job:

Here we don’t need remotely-initiated connections to the MSDTC service, so we’re not allowing remote clients or inbound communications; outbound is all we need.

By the way, if we don’t do this configuration of the MSDTC service, we’ll get the following error when we try to run our package:

[Connection manager "DestinationConnectionOLEDB"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8004D024.

and

[Connection manager "DestinationConnectionOLEDB"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D024 "The transaction manager has disabled its support for remote/network transactions.".

Configuring your Package for Transaction Support

There’s a pretty good Microsoft article on using transactions in SSL at http://msdn.microsoft.com/en-us/library/ms137690.aspx; I’d recommend it to you.
Here’s how it plays out.
You can set packages, loops, tasks, and sequence containers to use transactions, which really helps in ensuring consistency.
In our case, we’ll enable transactions at the package level, and then we’ll have the option of having our tasks be a part of that transaction:
Double-click on your package in SQL Server Business Intelligence Development Studio (BITS from now on). That opens the package. Now, right-click on the background and select properties, as below:

The properties data shows up on the right-hand side of the screen, and at the end of the list of properties, you’ll see “TransactionOption.” This is how we tell SSIS how to utilize transactions in the execution of the package. The options are as follows:

Required – Either start a new transaction, or join the transaction that was started by the parent to this object
Supported – Join a transaction, if the parent container was a part of one, but don’t start one. This is the default setting.
NotSupported – Don’t join a transaction, under any circumstances.

We’ll set the TransactionOption for our package to “Required.”

Now we can click one of our tasks, like the “Backup Original Table” task, and we’ll see that, since its setting is “Supported,” it’ll be a part of our package’s transaction.
Here’s something to consider: there are only certain transactions that can be rolled back. This is especially important to remember when we’re wanting to allow the rollback of deleted data.
There are circumstances in which cannot rollback a drop table, which often is used for these operations. Instead, unless you’re looking at a *ton* of data, use a “delete from” SQL statement. A truncate command will work in most instances, but check the rollback process in a test instance before putting it in production. This will ensure your original data can be rolled back with the transaction.
That’s all that’s required. Now, if we have a failure at the end of the process, when data is imported, that failure will trigger a rollback of our delete task.

Disabling ASR on a Windows Server

2010-04-01T11:06:00.010-05:00

ASR stands for automatic server recovery. It's a service whereby the iLO features of a HP server will reboot it, should it detect that something has gone wrong with the operating system. Good idea, except when the detected failure isn't really a failure. Or you want to be able to troubleshoot the problem while it's occurring.
The most straightforward method for changing this setting is to boot the server into the BIOS setup (F9) screen, and browse to Server Availability –> ASR Status.
Sometimes, you’d like to make this change without downtime, however. It appears it’s possible. Unfortunately, it also appears that a reboot, at some point, still is necessary.

Disabling ASR on-the-fly requires a couple of things, one an installation, and one a configuration change.

The installation that's necessary is net-snmp, a set of snmp tools. They can be got here: http://www.net-snmp.org/ It's an install, rather than a set of standalone tools, but you can (and should) opt not to install the trap and snmp services.
Once that's installed, double-click on the SNMP Service on the target server. You should see something like the screen shot below:

In order to change the ASR setting, the appropriate community (name changed here to ‘community’) needs read/write access. So edit that line and change that setting. A restart of the service is necessary for this change to take effect.
Once you've made that change, you can use the snmpget and snmpset commands to disable ASR.
To read the current setting for ASR, run snmpget:

C:\>snmpget -c community -v 1 localhost .1.3.6.1.4.1.232.6.2.5.1.0

SNMPv2-SMI::enterprises.232.6.2.5.1.0 = INTEGER: 4

Three is disabled. Four is enabled. So we can see that ASR is enabled on the system above. Let's change that.

C:\>snmpset -c community -v 1 localhost .1.3.6.1.4.1.232.6.2.5.1.0 i 3

SNMPv2-SMI::enterprises.232.6.2.5.1.0 = INTEGER: 3

And now we can double-check our setting:

C:\>snmpget -c community -v 1 localhost .1.3.6.1.4.1.232.6.2.5.1.0

SNMPv2-SMI::enterprises.232.6.2.5.1.0 = INTEGER: 3

It does appear that a reboot is necessary for this change to take effect, though (HP System Management Homepage still reports ASM as enabled after this change). That’s unfortunate, and if anyone has a notion if this will take effect without a reboot, it’d be great to hear about that.

Adding an Oracle home to an agent inventory

2010-01-14T11:04:00.009-06:00

When an Oracle inventory is saved in a non-standard location, the Oracle Grid Control agent can be unable to enumerate the software that is in that Oracle Home. This is true even when it can find the home itself.
In OEM, you’ll run into an error like that below when you click on the home in the Targets list:

Error Could not find Oracle Home <ORACLE_HOME> in the inventory collected for <hostname>

We’ll use TESTSRV2 as a troubleshooting example.

Locate the oraInst.loc file

The oraInst.loc file contains the inventory for all of the Oracle software. Normally, Oracle maintains a single copy of this file, but when one is saved in a non-standard directory, it can get left out.

The first thing we’ll do is find out where all of the oraInst.loc files are located.

[root@testsrv2 ~]# locate oraInst.loc

/oracle/install/920/scripts/silent/oraInst.loc

/oracle/install/817/scripts/silent/oraInst.loc

/OLD_TREES/a18_1/oracle/install/920/scripts/silent/oraInst.loc
/OLD_TREES/a18_1/oracle/install/817/scripts/silent/oraInst.loc

/app/oraInventory/oraInst.loc

/app/oracle/product/920/oraInst.loc

/app/oracle/product/102/oraInst.loc

/app/oracle/OracleHomes/agent10g/oraInst.loc

We can see that there are a lot of oraInst.loc files on this server. The file in the OracleHome/agent10g directory is the agent inventory. Some looking shows that the oraInst.loc file in the /app/oracle/product/102 directory is the most recent of all of the others, so we’ll use that one.

Add the additional inventory file to the agent search

There is a file that the agent uses to add inventories to its search list. This is the OUInventories.add file in the <AGENT_HOME>/sysman/config/ directory. So we edit it:

[root@testsrv2 ~]# vim /app/oracle/OracleHomes/agent10g/sysman/config/OUIinventories.add

[root@testsrv2 ~]#

And add the appropriate line:

Save that file and exit the editor.

Restart the agent

This change requires a restart of the agent:

/app/oracle/OracleHomes/agent10g/bin/emctl stop agent
/app/oracle/OracleHomes/agent10g/bin/emctl start agent

Refresh the Host Configuration

Having restarted the agent, we now have to tell Grid Control to refresh its list of the software on that host:
Navigate to Depoloyments -> Refresh Host Configuration

Add the host to the “selected hosts” pane and click on the “refresh hosts” button. This will populate the appropriate agent data, and you’re done.

Oracle Database loses its OEM configuration after a Cold Backup

2010-01-04T13:33:00.004-06:00

This was an annoying problem that took awhile to track down. In short: after our scheduled cold backups, an Oracle (11g) database would lose its configuration in Oracle Enterprise Manager. It would present a "Metric Collection Error" that would go away after reconfiguring the database. The fix, as it turned out, was pretty simple, but it took awhile to tease out. The problem was that the trace directory (bdump in 10g) was too full. Specifically, the metric collection (the process by which OEM gathers data about the database) was timing out. We ruled out performance problems on the database side; the system is not utilized much at all. Instead, we discovered that because there were a lot of files in the trace directory (> 31k), it was taking a long time for the OEM agent to get to the alert log, which is one of the metrics that it collects. This was hinted at in the emagent.trc file:

2010-01-04 13:01:53,087 Thread-47647632 ERROR TargetManager: TIMEOUT reached in computing dynamic properties for target TESTDB,

oracle_database::compute timings were [decideIncludeDB:0-0] [SystemTablespaceNumber:0-0] [SysauxTablespaceNumber:0-0 ... 

[DeduceAlertLogFile:1-1] [GetCPUCount:1-1] [EnabledFeatures:1-1] [GetOSMInstance:1-1] [GetNLSParam:1-1] [GetAdrBase:1-(1)]

So you can see above that one of the things it was trying to do was get at the alert log. It took a long time to enumerate all of the small files in the trace directory, so we shut down the instance, cleared out the trace directory, and restarted the instance. That took care of the problem. In troubleshooting this problem, we also increased the dynamic properties timeout setting (dynamicPropsComputeTimeout_oracle_database) in the emd.properties file (in [agent_home]/sysman/config), changing the value from the default (120) to a larger setting (240). That did not help, though it's a good troubleshooting step, should you run into a similar problem.

Installing Oracle Enterprise Manager 10.2 on Windows Server 2008

2009-12-01T13:24:00.007-06:00

One would suppose that installing OEM on Windows Server 2008 would be like installing it pretty much in any other Windows environment; Oracle did a pretty good job of making it easy to install and run out-of-the-box under earlier versions of Windows, so it should be easier with the latest version of OEM and Windows, correct?
Wrong. There are a variety of reasons, and we’ll run through them in this exercise, as we install Oracle Enterprise Manager 10.2.0.5 on Windows Server 2008 (NOT R2; this is important!).
As an added bonus, we’ll get the OEM repository database up to 11g.

Make sure you’ve got the right operating system

Start with the OS: Windows Server 2008, 32-bit (OEM is a 32-bit application, and putting it on a 64-bit system is asking for trouble). Windows Server 2008 R2 begins the march to 64-bit only, so make sure you're not installing it.

Install OEM 10.2.0.2

First, we have to download OEM 10.2.0.2, as that’s the latest version that is available as something besides an upgrade. You can get that install here: http://www.oracle.com/technology/software/products/oem/index.html. Note that 10.2.0.3 and above are only available as patch installers, rather than as a full installation.
Don’t install OEM, yet.

Install Oracle Database 10.2.0.3

The database that ships with OEM 10.2.0.2 is v10.1.0.4, which doesn't work on Windows Server 2008. So you have to create the repository database yourself.
If you find yourself installing OEM and getting an error starting the database (installation fails when configuring the database), this is your problem. You’ll also see the following in the application event log:

Faulting application ORACLE.EXE, version 10.1.0.4

Again, this is because earlier versions of Oracle don't work on Server 2008; make sure you've downloaded the correct version as below.
Installation files for Win 2008 32-bit can be downloaded here. Note that there is a separate installation for Vista and Windows Server 2008; make sure you get that.
Don't create a database as a part of the installation.
After installation, run the Database Configuration Assistant and create a basic database without configuring it for Enterprise Manager console.
Once the database is created, connect to it as sys and run the following commands to prepare the system for OEM. Note that the below commands assume you’re using an spfile. Make sure you’ve created one before running this.

alter system set session_cached_cursors=200 scope=spfile;
alter system set aq_tm_processes=1 scope=spfile;
alter system set job_queue_processes=10 scope=both;
@?/rdbms/admin/dbmspool.sql;
shutdown immediate;
startup; 

Run Net Configuration Assistant

This will create the listener.

Start -> All Programs -> Oracle - OraDb10g_home1 -> Configuration and Management Tools -> Net Configuration Assistant

Net Configuration Assistant won't update your tnsnames.ora file for your database; you'll need to make sure you do that. Net manager will, should you want to use a GUI to get that done. You can, of course, use other net naming methods, as well…

Install OEM with the Option for using an existing database

Tell setup not to check for system prerequisites

Windows Server 2008 didn't exist when 10.2.0.2 was created, so when installing, you've first got to tell it not to check system prerequisites:


setup.exe -IgnoreSysPrereqs

Otherwise, you'll get this error:


Checking operating system version: must be 4.0, 5.0, 5.1 or 5.2. Actual 6.0
Failed <<<<<<<<

Set the TimeZone

This seems silly, but it appears to have been a fatal error in bringing up the agent. If the installation fails at configuring the agent, this might be the problem. Here is the relevant section of the log at c:\oraclehomes\agent10g\sysman\log\emagent.nohup

Tue Nov 17 12:19:39 2009::The agentTZRegion value in C:\OracleHomes\agent10g/sysman/config/emd.properties is not in agreement with what agent thinks it should be.
Please verify your environment to make sure that TZ setting has not changed since the last start of the agent.

So open the emd.properties ($OracleHome\agent10g\sysmand\config\emd.properties) file and change the agentTZRegion line from GMT to the appropriate time zone code.

Change the local firewall settings

Open ports 1159, 4889, and 1521 to allow Oracle and OEM to communicate.

Test OEM 10.2.0.2

Version 10.2.0.5 requires that 10.2.0.2 be working before installing it, so make sure OEM is providing basic functionality before you start with the 10.2.0.5 installation.

Upgrade OEM to 10.2.0.5

Once 10.2.0.2 is working, run the install to upgrade it to v10.2.0.5. Stop enterprise manager Start -> All Programs -> Oracle application server -> Stop EnterpriseManager
If you don't do this, you'll get the following error:

Management Servers cannot be connected to the management repository during upgrade

They tell you to wait four minutes after stopping the server, and they're not kidding; you really do have to wait. Run setup.exe from the OEM download.
When it asks for the IAS_ADMIN password, this is the password you entered to secure the agent communication.

Once the upgrade to 10.2.0.5 is complete, make sure that OEM still is working. (Browser to your server on port 1159.) Having verified that, we'll start to upgrade the database.

Upgrade the database to 11g

Now that we’ve got OEM up to 10.2.05, we have the option of running the back-end database on 11g. Here’s how to get that: First install 11g on the same system without creating a database into a new Oracle Home. Suggested: c:\oracle\product\11.1.0 use c:\oracle as the Oracle Base location.
Once installation is complete, run the DBUA (DataBase Upgrade Assistant) and select the instance you want to upgrade. Don't move the database files when doing the upgrade unless you had them stored in the oracle_home from 10g. DBUA takes a long time to run.

Re-set the Sys password and parameter

After upgrading to 11g, the agent likely will not be able to connect to the OEM instance anymore. This is pretty easy to fix: in the OEM instance, make sure the following parameter is set:

alter system set remote_login_passwordfile=exclusive scope=spfile;

Then make sure you've got a pwd file:

c:\oracle\product\11.1.0\db_1\dbs\orapwd file=C:\oracle\product\11.1.0\db_1\database\pwdSID.ora password=xxxxxx entries=5

Note that the above command has to be run as administrator (right-click on the cmd icon and select "Run as administrator"). Now log in to OEM and click on Targets -> All Targets. Select the database instance and click the configure button (see below)

Re-set the sys password (making sure it's set to connect as sysdba) and save your changes. Now restart the instance and the agent; you should be good to go.

Troubleshooting

If installing OEM fails with a "RepManager Create Repository Error = 14" message, then you probably need to get rid of some DB users and objects. The following command will do this (from support doc ID # 358627.1)

repmanager [hostname] 1521 [SID] -action drop

There's another gotcha in that command, though: it has to be run with Windows' administrator privileges. If you see the following when attempting this command, you need to start a command console as administrator.


Enter repository user name : sysman
Getting temporary tablespace from database...
Found temporary tablespace: TEMP
Checking SYS Credentials ... Access is denied.
Failed
SYS credentials or connect string is invalid.

An Introduction to SSIS - A Beginning Step-by-Step Tutorial

2009-09-04T15:54:00.020-05:00

SQL Server Integration Services (SSIS) is a *really* powerful data transformation and import tool; it allows for all kinds of data manipulation, both between databases and within them.
The problem is that it’s not entirely intuitive; the learning curve is steep. But you really shouldn’t let that stop you from trying it out: once you’ve got the basics, it’s really quite accessible.
In this series of posts, we’ll do some basic, step-by-step data manipulation with SSIS, starting with importing data from a CSV file into a SQL Server 2008 database. We’ll move on to copying data between Oracle and SQL Server.
For more complicated data, bulk insert allows for some reasonably full choices. You can read about using it in my post here.

Importing data from a CSV file into SQL Server

SQL Server allows for a few ways to import data from a text file, most directly with the import wizard, which is a pretty accessible way to import uncomplicated data. This, by the way, uses SSIS in doing the import, though it wraps it all in a wizard.
SSIS, though, offers the most flexibility, both in terms of data source and destination, as well as what you’d like to do with the data as you’re copying it.

How do I get SSIS?

SSIS is included with SQL Server 2005 and 2008. It’s installed by default, and you can specify its installation specifically by selecting the “Integration Services” components of SQL Server when you’re doing the installation.
Note, by the way, that SSIS does not require a SQL Server instance in order to run.

How do I use SSIS?

Ah, here is where the rubber hits the road. The short answer is: you develop packages for SSIS to use with the SQL Server Business Intelligence Development Studio (whew!). We’ll call it BIDS from now on. Packages that you create are run either through the development studio or on a server with Integration Services installed; we’ll begin with a simple and uncomplicated import from a standard, unformly-formatted CSV file:

Importing Uniform Data using SSIS

Let’s begin with a CSV file that looks like this:

Last Name,First Name,Phone,Age
Lastname1,Firstname1,555-555-1111,25
Lastname2,Firstname2,555-555-1112,30
Lastname3,Firstname3,555-555-1113,55
Lastname4,Firstname4,555-555-1114,84
Lastname5,Firstname5,555-555-1115,22
Lastname6,Firstname6,555-555-1116,44
Lastname7,Firstname7,555-555-1117,66
Lastname8,Firstname8,555-555-1118,31
Lastname9,Firstname9,555-555-1119,30
Lastname10,Firstname10,555-555-1120,21

Let’s save this to a file named test.csv.

Launch BIDS

Click on the start button and browse to All Programs –> Microsoft SQL Server 2008 –> SQL Server Business Intelligence Development Studio.
I hope I don’t have to type that whole name again.

When it launches, you’ll see a screen that looks something like the image to the right (click for a larger version).
In the top-left pane, you’ll see a few options (below). Click on the Create Project link to start a new project.

A new window will open, asking what kind of project you’d like to create. Select “Integration Services Project” and give it a name. You can choose a shorter path to which to save your project, as well. Click on OK to begin designing your project.

Create A Source Connection

When you click on OK, you’ll see a window like the one below. What we want to do is create a new connection, which is just another way of saying that we want to set up a data source. In our case, we’re going to use a CSV file, so right-click on the Connection Manager pane at the bottom and select “New Flat File Connection.”

That will bring up the following page. I’ve gone ahead and filled in the details.

Basically, we’re telling SSIS that we want to set up a data source using this file (test.csv). You can see that it’s (by default) set to parse the file as a “delimited” text file, which is what a CSV file is. Note that the first row in our file contains the names of the columns; as a result, we’ve checked “Column names in the first data row.” Leave that blank if your data doesn’t have column names in the first row.
Likewise, SSIS allows you to skip n rows before looking at the data. This is useful, especially, when using automated processes that insert a lot of commands or comments at the front of the file.
One other piece of this screen is very useful, such that it’s worth digressing just a bit: the “Text qualifier” field allows you to define a character that will set a field as a text string. This usually is a double quote. Lots of times you’ll run into a CSV that looks something like this:

FirstName,LastName,Birthdate
Brad,Benson,3/3/1970
Sam,"Watson, Jr.",7/20/1971

(This is an example from a comment on another post on bulk insert here.) You can see the problem: there’s a comma in the person’s first name, and it’s set apart by quotes. Bulk insert will parse that first name as two separate fields. Using this Text qualifier field, SSIS will render the above CSV correctly, thus:

FirstName	LastName	Birthdate
Brad	Benson	3/3/1970
Sam	Watson, Jr.	7/20/1971

OK, back to our task.

You can take a look at the settings in this window; for our purposes, the defaults should work nicely. You can preview the data by clicking on the preview link on the left-hand side; this should show you the contents of our CSV file, broken down into a table.

Create a Destination Connection

Now that we’ve got the source defined, we need to do something similar for the destination. Right-click in the connection manager window again and select “New OLE DB Connection”. Click on the “New” button to create a new connection.
When you do, you’ll see a window like that below:

Fill in the server name and database name appropriately and click on OK. I’ve selected the adventureworks database. You’ll notice that there’s not a place here to name the connection; that’s OK, because it’ll be named SERVERNAME\.DBName.

Create a Data Flow Task

SSIS uses workflows to step through its process, and most of your work will be done in the data flow task. Note that the data flow task will contain other sub-tasks.
While in the Control Flow tab, mouse over the Toolbox in the top-left corner (highlighted below).

When you do, the Toolbox will open, as below:

Click on the “Data Flow Task” item, and drag it to the main screen. You’ll see an icon like that below. Double-click on that icon to open it.

When you double-click on the task, it appears that you have just erased it, but in reality, you’ve changed to the Data Flow tab; look at the top of the screen, and you’ll see that you’re no longer on the control flow tab. You can go back and forth between these as you choose.

Add the Source task

Now that we’re editing our data flow task, click on the toolbox again to drag the Flat File Source to the main screen. By default, it’ll be populated with the CSV connection we set up earlier.

One thing to note is the “Retain null values from the source as null values in the data flow.” This is disabled by default, which means that null values in the source will be treated as empty strings, instead.
You can click on the Preview button to double-check your file.

Create the Data Destination

Now that we’re reading the data, we need to insert it into the database. Click on the Toolbox again and scroll down to the Data Flow Destinations section. Drag the OLE DB Destination to the main window.
Before we can write any data, we have to tell SSIS how to map the data. In our example, we’re going to create a new table in the Adventureworks database, but the process for using an existing table is the same.
First, click on the Flat File source icon and drag the green arrow to the OLE DB Destination icon.
Next, double-click on the Destination icon to edit it: Notice that this also allows you to keep nulls. In our example, we’re going to create a new table, but if you have a table already created, you can select it from the table drop-down menu.

Clicking on the New… button will bring the the following screen:
Change the table name in the SQL statement (I chose “importTest”). Of course, we’d also normally want to change the data types; the age field would be better as an integer field, and we wouldn’t normally want the phone number to be a 50-character field. We’ll leave it with defaults for our demonstration, however. Click on OK.
Finally, we need to set the mappings: click on the Mappings option on the left-hand side of the screen:

The defaults should be fine for our purposes, so you can click on OK.

Test it out

We should be good to go. Hit <F5> to run your package. You should see each step turn green as the data is read and written.

We’ll spend some time in another post on scheduling SSIS packages; there are a lot of things that can complicate that process.

ORA-29275 when Accessing a UTF8 Database with SSIS

2009-08-05T22:25:00.005-05:00

So when I first started looking into SQL Server Integration Services (SSIS), I was told that the learning curve was steep, and that it was worth it to learn what you're doing with it. Truly said. SSIS presents a myriad of possibilities for data, and once you get your head around some of the terminology, creating simple transformations and data imports is a snap. But what about when the problems you encounter span two different products? What if one is Oracle? What if it's 64-bit, on Linux? UTF8? That's really what SSIS is for, and it's possible. But. Lots of Googling. Here's one roadblock I encountered in importing data from an Oracle database into SQL server, along with an unsatisfying workaround. But it does work, and it's easy. If you know of a better solution, I'd love to hear from you!

The Problem

So here's the problem in a nutshell: the Oracle database uses a utf8 character set. SSIS, when connecting with either the OLEDB or ADO.NET data sources, would use something else. What, precisely, I'm unable to discern. I can say that the Unicode setting was set to true in the data manager. This was manifest by a startling Oracle error during the load from the data source: ORA-29275, which is a "partial multibyte character" error. This means that the data doesn't fit the database's character set, which (one assumes) is terrible: such data is almost by definition corrupted, and getting it back reliably is a tricky proposition. Oracle says, basically, that you've got bogus data when you see this error. I was prepared to believe that, as this error occurred even in a simple select statement from the DB server itself.

The Clue

The curious thing about this situation is that, in trying to figure out what was going on, another user had logged into the server using a different OS username. When he connected to the database using the same Oracle user ID, he didn't get any errors. Aha. Environment. NLS_LANG, to be exact. Setting that to American_America.UTF8 took care of the error on the server, and on clients running SQLPLUS, to boot. All should be well, correct? No. The SSIS package continues to fail. Oh, yes: the registry. Don't forget that Oracle stores client NLS data there, as well: HKLM/Software/Oracle/$ORACLE_HOME/NLS_LANG Watch out for any dangling NLS_LANG settings in HKLM/Software/Oracle That surely will fix it, right? Sadly, it didn't, and a desperation reboot didn't help.

The Work-Around

So Google leads me to hints that ADO.NET and OLEDB from Oracle don't really pay much attention to the local NLS_LANG settings. That appears to be the case, or, at least, they don't get their settings from the same place everything else does. So, I return, sadly, to ODBC. And it works! It works well. But it's so non-portable, and, let's face it: ODBC is not anything new and shiny; it'd sure be nice to have all of our SSIS packages all new, self-contained, and .NET-ed. So if you, like me, run into this problem, know that ODBC can be your friend. If you, unlike me, know of a better solution, please let us know! I'll post updates as I encounter them.

Creating a Floppy Image in Windows

2009-08-03T15:14:00.004-05:00

It's not too often any more that we need a floppy drive image created, but there are times (such as when accessing a server through iLO) when it's handy to have a virtual floppy drive. There are a lot of shareware and commercial programs out there that will create a .img file from other files on your file system, but I had a hard time finding a free floppy image creation package. BFI (Build Floppy Image), thank goodness, still is around. It's a very easy-to-use floppy image creation command line tool that is fully free. It's old, and it's unsupported, but it works. Rather than hosting the files here, I'll link to BFI's home page. If you find that the page or files are down, let me know, and I'll post it here. In short, what you do is put the files you want to write to your image in their own directory. Here's an example of its usage. Say I want to create a floppy image file at c:\temp\myfloppy.img, containing all of the files in c:\temp\floppyfiles. The command I'd use to do this is:

bfi -f=c:\temp\myfloppy.img c:\temp\floppyfiles

That's it. Enjoy your new (free and legal) floppy disk image!

Installing Oracle on RHEL 5 (32 and 64 bit) - Part 3

2009-07-27T22:20:00.010-05:00

In part one of this series, we got the operating system ready for the installation of Oracle. In part two, we got Oracle installed and running. In this, our final (for now) post on Oracle and Linux, we'll look at some of the tweaks that make it better, as well as some of the surprising bumps one encounters when using Oracle on Linux.

Troubleshooting

ORA-00845 - MEMORY_TARGET not supported on this system

This error is common, and it occurs most frequently when the Linux /dev/shm mount point isn't large enough. Specifically, your SGA and PGA are sized such that there isn't enough space in /dev/shm for the instance to start. So, to fix it:

mount -t tmpfs shmfs -o size=1300m /dev/shm

where 1300m is whatever size that will at least cover your MEMORY_TARGET parameter. Once you've got a value for /dev/shm set that works, you can add it to /etc/fstab, such that it'll be a permanent change. Edit /etc/fstab, and add the following line, adjusting the size to fit your environment:

shmfs /dev/shm tmpfs size=1300m 0 0

Enable Arrow Keys in SQLPlus

One of the best things about using SQLPlus is the ability to up arrow through your command history. As it turns out, though, this functionality isn't available in sqlplus on Linux. It makes for a most unsatisfying experience. Fortunately, there's a simple program -- rlwrap -- that can fix this for us.

Download rlwrap

Download the rlwrap archive here, and extract it to a temporary directory. The INSTALL document is pretty straightforward for compiling and installing it. Once it's installed, you can create an alias using rlwrap, such that it'll provide a keyboard wrapper for sqlplus, allowing you to use the arrow keys.

Create the rlwrap alias

There are two thoughts on what to do with the alias for sqlplus. I'll lay them out, and you can decide which is the better option. Option One is simply to create an alias called "sqlplus", in essence replacing the program call with the alias. This has simplicity in its favor: whenever you or anyone else log in to the system to run sqlplus, it'll behave as it should, and you won't have to think about it. But there's a downside, as well: when someone calls a program by name, it's usually expected that the program is what is running. When you create an alias using the program's name, you introduce the possibility of confusion in troubleshooting a problem later on. Should something happen with rlwrap or one of the libraries it relies upon, sqlplus (unless called with a fully qualified path) could quit working. The risk of confusion is especially great if the person doing the troubleshooting isn't the person who set up the alias: then there's no chance of remembering that the alias is there. Here, should you choose option one, is how you'd set up the rlwrap alias (we'll throw in rman as a bonus):

alias sqlplus='rlwrap sqlplus'

alias rman='rlwrap rman'

So enter Option Two. This is just creating an alias -- just as above -- with a name besides sqlplus. Using a name that isn't the program name has the advantage of avoiding any possible confusion about what you're doing: if it quits working, it's easy to see if the problem lies with sqlplus or with the alias. The downside is that it's a different command. Sqlplus is the program one uses to execute SQL in Oracle; it will not occur to anyone to use something else. Here's something like what you'd use to create an alias (we'll call them sqlp and rmanp, but they can be pretty much anything):

alias sqlp='rlwrap sqlplus'

alias rmanp='rlwrap rman'

Both options offer something good, and both have a downside. Pick the one that works for you, and make sure your colleagues all know about the changes you made.

A third option

There exist a handful of drop-in sqlplus replacements that many opt for, as well. gqlplus is one of these. For my part, I prefer to stay away from compiled replacements for sqlplus; whatever wrapper goes in front of the application, when it's all said and done I want the Oracle-supplied application communicating with the database.

Installing Oracle on RHEL 5 (32 and 64 bit) - Part 2

2009-07-26T09:41:00.011-05:00

In part one of this series, we got the operating system ready for the installation of Oracle. In this post, we'll actually install Oracle and get it up and running. In part three, we'll look at some of the things you can do to make Oracle a bit more usable on the Linux platform. Oracle recommends using the GUI installer. I think they're right: it's the most accessible way to get Oracle installed. If you only have remote access to your system, and SSH is the only remote access you've got, you can set up VNC reasonably quickly to have remote GUI access to your system.

Download and extract Oracle

I'll leave it to you to download the appropriate package from Oracle. The free version of Oracle (Express Edition), at the time of this writing, isn't available for 11g, so 10g is the latest version you'll be able to get. These instructions will work for that, as well. Unzip the install files to a temporary location.

mkdir /tmp/oracle
cd /tmp/oracle
unzip linux_11gR1_database.zip

Launch the Installer

The installation needs to happen as the Oracle user. Either log in as the user Oracle, or issue the following command:

su - oracle

Then, from the temp location that has the unzipped archive, run the installer:

./runInstaller

Most of the defaults will be sufficient for your needs; I'd choose *not* to install the starter database, unless you're wanting to use it for tutorial purposes. As an aside, it's worth noting that Oracle licenses all of its products for development/tutorial purposes without charge:

All software downloads are free, and each comes with a Development License that allows you to use full versions of the products at no charge while developing and prototyping your applications (or for strictly self-educational purposes). In some cases, certain downloads (such as Beta releases) have licenses with slightly different terms. You can buy products with full-use licenses at any time from the online Store or from your sales representative. (from http://www.oracle.com/technology/software/index.html)

Set up the Listener

The installer does an OK job at setting up your TNS listener, but it's worth knowing how to edit your tnsnames.ora file both for troubleshooting and for adding instances to it as you install them. Below is a sample tnsnames.ora

# ### # #

#  11.1 64-bit
##

#  Test Oracle Database #

# ##############

oratst =
 (DESCRIPTION =
   (ADDRESS_LIST =

 (ADDRESS = (PROTOCOL = TCP)

(HOST = oraclsrv1.example.com)(PORT = 1521)))

(CONNECT_DATA = (SID = ORATST)

(SERVER = DEDICATED)))

The "oratst" line is the name of the database, as you want it accessed from the client. It's most straightforward to keep this the same as the instance SID, but you can do some cool sleight-of-hand stuff with a TNSNAMES.ORA file in switching an application from one DB to another with minimal downtime.

That is to say: that first line with the database name can be pretty much whatever you want it to be.

The ADDRESS line lists the DB host, and the connect_data tells the client how to address the database.

The SERVER setting bears a little explanation. It can be either "DEDICATED" (as above) or "SHARED". Dedicated means that each connecting client gets its own Oracle process, while Shared means that individual connections share a pool of connection processes. Most folks have little to no reason to use the shared connection setting. This is a database setting, and the TNSNAMES.ORA file simply tells the client which is being used.

For more in-depth discussion about dedicated vs. shared servers, check out this link. It's a pretty good run-down of the strengths and (mostly) weaknesses of shared servers.

Test your connection

Try the following command to make sure that you've got your tnsnames set up correctly:

tnsping <DB_SID>

where DB_SID is the name listed for your database in your tnsnames.ora file. You should get something like the output below:

[oracle@tcudspc1 admin]$ tnsping oratst

TNS Ping Utility for Linux: Version 11.1.0.6.0 - Production on 26-JUL-2009 20:39:42

Copyright (c) 1997, 2007, Oracle.  All rights reserved.

 Used parameter files:

Used TNSNAMES adapter to resolve the alias
(CONNECT_DATA = (SID = oratst)(SERVER = DEDICATED)))U)(PORT = 1521)))

OK (10 msec)

If you get something else, there's a problem, most likely either with your tnsnames.ora file. It's also very possible that your listener process isn't running. Which brings us to part 3: stopping and starting Oracle, and making it easier to use.

Installing Oracle on RHEL 5 (32 and 64 bit) - Part 1

2009-07-23T21:53:00.031-05:00

With the then-hyped Oracle Enterprise Linux rollout, and Ellison's evident disdain for Microsoft, you'd have thought that Oracle would go out of their way to make it straightforward to install and use Oracle database on Linux. It's not so, unfortunately. Not that it can't--or shouldn't--be done, mind you: it works great; you just need some persistence. This is part one of a three-part series on Oracle on Linux. This post looks at getting the OS ready for Oracle. Part 2 looks at installing the database software, and Part 3 talks about some of the things that can be done to make running and maintaining Oracle easier on Linux. Oracle has a document here that does a pretty good job of outlining the steps necessary to install Oracle on linux. I quibble with some of their instructions, particularly their directions to use the package rpms from the Oracle install CD. If they're going to go to the trouble of providing the requisite rpms, it seems like they'd also install them during the installation process, if they're needed. Instead, it seems like a *much* better option to install them from your repository (CENTOS or RedHat are most pertinent to this guide). They also have you using rpm, which is fine, but it won't find dependencies for you like yum will. In any case, I do recommend that document for additional information. It's my hope that this series will help navigate the installation process and make it easier. I've broken this into several posts so as to keep the length of each one a little smaller, anyway. In this post, we'll get the OS prepped and ready for the
Oracle software installation.

Prerequisites

It's important to make sure you've got all the necessary software installed on your server before beginning the installation of Oracle. They've done a better job with 11g of testing for the existence of prerequisite software, so you get some better error messages when you are lacking a particular package. Here are the prerequisites that you don't have with a default RHEL install:

compat-libstdc++-33
elfutils-libelf-devel
gcc
gcc-c++
glibc-devel
libaio-devel
libstdc++-devel
sysstat
unixODBC
unixODBC-devel

So:

yum install compat-libstdc++-33 elfutils-libelf-devel gcc gcc-c++ glibc-devel libaio-devel libstdc++-devel sysstat unixODBC unixODBC-devel

Now, another thing that has gotten better with 11g is the documentation about prerequisites for 64-bit systems. It turns out that you've got to have both the 64-bit and 32-bit versions of some packages in order for things to go smoothly. The 64-bit prereqs that aren't installed as a part of a standard RHEL install are:

compat-libstdc++-33
compat-libstdc++-33 (32 bit)
elfutils-libelf-devel
gcc
gcc-c++
glibc-devel
glibc-devel (32 bit)
libaio-devel
libstdc++-devel
sysstat

Which translates to

yum install  compat-libstdc++-33 compat-libstdc++-33*.i386 elfutils-libelf-devel gcc gcc-c++ glibc-devel glibc-devel*.i386 libaio-devel libstdc++-devel sysstat

Set up the OS for Oracle

These, again, seem like things that could be done easily by the installer. I know: you don't want to assume folks are installing as root. But why not, instead, require that the installation be done as root and allow the installer to choose these things from the UI? Again, I'm not saying these things are hard, but if you're wanting to increase your install base... Here the instructions for Oracle are quite good, BTW. Create the user account and install directory:

/usr/sbin/groupadd oinstall
/usr/sbin/groupadd dba
/usr/sbin/useradd -m -g oinstall -G dba oracle

mkdir -p /oracle
chown -R oracle:oinstall /oracle
chmod -R 775 /oracle
>passwd oracle

Oracle uses /u01/app/oracle for their default installation location. Seems to me that /oracle makes more sense. In any case, pick what works for you, and use that in the commands above.

Set kernel parameters and user limits

Again, the Oracle docs are quite helpful on this front:

cat >> /etc/sysctl.conf

<<EOF

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

fs.file-max = 65536

net.ipv4.ip_local_port_range = 1024 65000

net.core.rmem_default=4194304

net.core.wmem_default=262144

net.core.rmem_max=4194304

net.core.wmem_max=262144

EOF

/sbin/sysctl -p

cat >> /etc/security/limits.conf <<EOF

oracle soft nproc 2047

oracle hard nproc 16384

oracle soft nofile 1024

oracle hard nofile 65536

 EOF

 cat >> /etc/pam.d/login

 <<EOF

session required /lib/security/pam_limits.so

EOF

Oracle has you set some OS limits on the Oracle user by editing the main system profile file. Their script generates a "unary operator expected" error (at least under some circumstances), so it seems a lot more direct to add that limit to the Oracle user's .bash_profile (assuming you're using bourne). So add the following to /home/oracle/.bash_profile

ulimit -u 16384 -n 65536

umask 022

I find it frustrating not to have tools like SQLPLUS and TNSPING in the path, and I want the Oracle environment variables set automatically, so add the following (modified to match your environment, of course) to .bash_profile, as well. If this is a system that hosts a lot of databases, it might not make sense to set the ORACLE_SID variable; that's up to you. Oracle's recommended method for taking care of this is to use the oraenv script, which is at /oracle/product/11.1.0/db_1/bin/

export ORACLE_BASE=/oracle

export ORACLE_SID=<db_sid>

export ORACLE_HOME=/oracle/product/11.1.0/db_1

export PATH=$PATH:/oracle/product/11.1.0/db_1/bin/

Click here to see the code to use for korn shell and c-shell.

If you're using korn (ksh), add the following to /home/oracle/.profile

ulimit -p 16384
ulimit -n 65536
umask 022

And, finally, if you're using c-shell, add the following to /home/oracle/.login

limit maxproc 16384

limit descriptors 65536

umask 022

That should get things ready for the Oracle installation. In Part 2, we'll get Oracle installed.

ORA-00911 when writing a query for SSIS

2009-07-13T16:32:00.005-05:00

Coming from the Oracle world, I'm in the habit of putting a semicolon at the end of all of my SQL queries. Not a good idea when you're writing an Oracle query for import/export in SSIS (SQL Server Integration Services). The problem is that SSIS takes care of that for you, and you'll get an entirely unhelpful "ORA-00911: invalid character" error message. Just remove that final semicolon, and you should be in OK shape.

Converting a Physical Linux system to a VMWare VirtualCenter VM

2009-05-15T22:34:00.011-05:00

This is a daunting task. At least, it is until you figure out how to do it. Once you've got the steps, it's quite simple, and relatively quick. We'll be importing a physical Linux (CENTOS 5) server -- using IDE drives -- into a VMWare VirtualCenter-managed host. So immediately, one thing needs to be made clear: the tools we're using in this exercise aren't free. There are a variety of tools out there that are free, and I may get around to exploring those for this situation, as well. In the mean time, however, we're talking about VMWare Enterprise Converter 4 (v3 works, as well), which isn't available (yet, anyway) without cost. So enough of the fine print: let's get to it.

Downloading the Converter Boot ISO

This seems like it'd be obvious, but it wasn't to me: Click on the downloads link at www.vmware.com, and instead of clicking on the download link for VMWare Converter, choose VMware Infrastructure 3, instead. Now click on the download link for VMware vCenter Server 2.5. This is where you log in (see: I told you it wasn't free). Having done so, you'll see a link to download VMware vCenter Converter BootCD for vCenter Server. That's what you want. I know: it was obvious to you; I'm just slow. Burn that CD, and we'll move on to the next step.

Boot the Converter CD

There aren't a lot of options for using the converter CD, so I'll skip much detail about it, unless someone requests more. There is one very important gotcha, however: Network Autonegotiation Speed. It doesn't do it well. At least, it doesn't do it well with all network cards. Here's the problem: VMWare simply took their Windows-only converter application and put it on a Windows PE boot disk. And the network drivers don't appear to be terribly robust, at least, not for all network cards. So here's what you do: when the system boots, choose to edit the network settings manually (if you've already gotten past that point, you can edit them from the Network Settings menu in the Converter application), and set the speed from auto to whatever speed your system supports (in my case with this system, 100Mb Full duplex). This will make things work much, much better. I'll note that, if your import process is taking a *really* long time, and failing often during the process, this is most likely your problem.

Edit your new VM

Try a quick Boot

Once the import process is complete, go ahead and try powering the system on. It almost certainly will fail with a kernel panic, as below:

Kernel panic - not syncing: Attempted to kill init!
VFS: Cannot open root device "LABEL=/" or 00:00
Please append a correct = "root=" boot option
Kernel panic: VFS: Unable to mount root fs on 00:00 

it may also say something like

VFS: Cannot open root device "VolGroup00/LogVol00" or unknown-block(0,0)

Depending upon your configuration. If not, you're done. You're probably not done, though; the problem we're witnessing here is that Linux is still looking for an IDE disk. That drive no longer exists: it's been converted to a SCSI disk, so we need to tell Linux how to read its new disk.

Change the VM SCSI controller type

Set your VM SCSI controller to use LSI Logic instead of BusLogic. VMWare says either will work, but I've had much better luck with LSI. Right-click on your VM and select Edit Settings. Click on the SCSI Controller and click on the Change Type button, if it's not already set to LSI Logic.

Boot to the Linux Install CD

Power your VM on and mount the ISO (or actual CD) for Disk 1 of the Linux install set. If you installed from a DVD, just use that. When prompted for boot options, type

linux rescue

When the boot process is complete, it will ask you if you want to mount your file system. Don't do it read only; just click on Continue. We'll change our root at this point, to make things easier:

chroot /mnt/sysimage

/mnt/sysimage, by the way, is where the linux rescue system mounts your original file system. If there is nothing mounted there, you have a problem. The best I can offer at this point is to power off the system and change the scsi controller to whatever it isn't set to right now. Having done that, there are a few files to edit, and then we'll re-create the boot image with the updated settings:

Edit the files

Edit the following three files and replace all occurrences of /dev/hda with /dev/sda (if you're coming from IDE). If you're coming from physical SCSI devices, you'll find, in addition to /dev/hda, /dev/cciss/c0d0. Change these to /dev/sda. If you're unsure. make a backup of these files first.

vim /etc/fstab
vim /boot/grub/device.map
vim /boot/grub/grub.conf

Now edit /etc/modprobe.conf:

vim /etc/modprobe.conf

While we're in here, VMWare suggests making sure the ethernet adapter has been updated: for each ethx (x is a number) alias in modprobe.conf, set the module entry to pcnet32. Now, since we're using LSI Logic for our SCSI controller, we'll add (usually; if the settings are in there, make sure their values are correct) the following:

alias scsi_hostadapter mptbase
alias scsi_hostadapter1 mptscsih

If you're using BusLogic, the above setting is BusLogic instead of mptscsih.

Create the new Boot Image

Now we're (almost) ready to create our new boot image. Looking at the files in /boot, you'll likely see a whole bunch of different initrd*.img files. One of those is going to be replaced by what we're about to do. Look in /etc/grub.conf to see which one:

cat /etc/grub.conf

Note the initrd*.img file that is listed in the above file, as well as the kernel version (usually the same). This is what we'll be using.

Fix a RedHat bug

If you're running RedHat Enterprise Linux (RHEL) or CENTOS, you're almost certainly going to run into a mkinitrd bug. Let's nip that before it comes up:

echo "DMRAID=no" > /etc/sysconfig/mkinitrd/noraid
chmod 755 /etc/sysconfig/mkinitrd/noraid

In short, the bug makes this happen when you run mkinitrd:

No module dm-mem-cache found for kernel 2.6.18-92.1.1.el5, aborting.

If you want more information on it, it's described here.

Run mkinitrd

Simply run mkinitrd -v -f followed by the /boot/initrd-*.img filename and then the kernel version that you noted from /etc/grub.conf above. In my case, it looked like this:

mkinitrd -v -f /boot/initrd-2.6.18-92.1.1.el5.img 2.6.18-92.1.1.el5

When that runs to completion, you should be able to boot. Note that you'll want to make sure that Grub boots to that version when you reboot your VM. To ensure that, when the grub message comes up "hit any key to enter boot menu", do so, and select the appropriate kernel from the list. Make sure you update your system after it has booted; you may have ended up with an older boot kernel than you want.

Microsoft Office 2007 button menu not dislaying

2009-05-10T10:38:00.004-05:00

A quick Google search will show that a few people here and there report instances in which the Office Button (the icon at the top-left-hand corner of Office 2007 applications) menu won't display when you click on it. If you do some digging, it turns out that you can navigate the menu with keystrokes, even though it's not appearing. The problem, it turns out, is with remote control software. We ran into this, and it was puzzling, both the problem and the fact that there isn't much out there that is easy to find (hence this post). We were using an older VNC (TightVNC 1.3.8, to be specific, with which I'm not enamored, anyway). It's unclear to me under what circumstances this occurs (we have many servers running that version of TightVNC, without exhibiting this problem), but using RDP (MS Remote Desktop) does clear up the problem, as does installing RealVNC.

Creating SSL Certificates with Multiple Host Names

2009-04-03T14:22:00.027-05:00

Creating an SSL Certificate with Multiple Hostnames

There's another article on creating wildcard certificates in apache (and here on IIS), but we've not discussed the possibility of having a single certificate answer to several hostnames (DNS cnames, and http host headers). This uses an SSL feature called SubjectAlternativeName (or SAN, for short). Not only is this possible, but it's reasonably easy. We'll talk about generating the certificates using openssl on linux, but openssl is available for Windows, also, and the procedure is the same. Note that Windows' selfssl utility doesn't provide the ability to generate a certificate request file with SANs, so if you want to use this on Windows, you'll need to use openssl or another ssl utility.

Generate the Certificate Request File

For a generic SSL certificate request (CSR), openssl doesn't require much fiddling. Since we're going to add a SAN or two to our CSR, we'll need to add a few things to the openssl.cnf file.

Edit openssl.cnf

Edit your openssl.cnf file (by default, in RHEL v5, at /etc/pki/tls/openssl.cnf).

vim /etc/pki/tls/openssl.cnf

You should see, about a th ird of the way down, a section that begins with [ req ]. This is the section that tells openssl what to do with certificate requests (CSRs). Within that section should be a line that begins with req_extensions. We'll want that to read as follows:

req_extensions = v3_req

This tells openssl to includ e the v3_req section in CSRs. Now we'll go own down to the v3_req section and make sure that it includes the following:

[ v3_req ]

# Extensions to add to a certificate request

basicConstraints = CA:FALSE

keyUsage = nonRepudiation, digitalSignature, keyEncipherment

subjectAltName = @alt_names 

[alt_names]

DNS.1 = kb.example.com 

DNS.2 = helpdesk.example.com 

DNS.3 = systems.example.com

You'll notice that in our example above, we've added three SANs. Note that whatever we put here will appear on all CSRs generated from this point on: if at a later date you want to generate a CSR with different SANs, you'll need to edit this file and change the DNS.x entries.

Generate a private key

You'll need to make sure your server has a private key created. It usually does (in/etc/pki/tls/private, normally called localhost.key), but it not, you'll need to create one:

openssl genrsa -out /etc/pki/tls/private/domainname.key 1024

Where domanname is the FQDN of the server you're using. That's not necessary, BTW, but it makes things a lot clearer later on. Do not enter a passphrase for this private key! Doing so will require a password to be entered each time apache starts, which, on the whole, is a terrible thing. Make sure that your ssl.conf file (/etc/httpd/conf.d/ssl.conf or whichever is the active httpd conf file) contains the line:

SSLCertificateKeyFile /etc/pki/tls/private/domainname.key

Otherwise, Apache won't have the information necessary to decrypt the SSL traffic.

Create the CSR file

Now we're ready to create the CSR.

openssl req -new -out /tmp/domainname.csr -key /etc/pki/tls/private/domainname.key  

Where /tmp/domainname.csr includes the FQDN of the server, and domainname.key is the private key referred to above. You'll be prompted for information about your organization, and it'll ask if you want to include a passphrase (you don't). It'll then finish with nothing much in the way of feedback. But you can see that /tmp/domainname.csr has been created. It's nice to check our work, so we can take a look at what the csr contains with the following command:

openssl req -text -noout -in /tmp/domainname.csr

You should see some output like below. Note the Subject Alternative Name section:

[root@domainname /]# openssl req -text -noout -in /tmp/domainname.csr

Certificate Request:

Data:
Version: 0 (0x0)

Subject: C=US, ST=Texas, L=Fort Worth, O=My Company, OU=My Department, CN=server.example.com/emailAddress=notreal@example.com

Subject Public Key Info:

Public Key Algorithm: rsaEncryption

RSA Public Key: (1024 bit)
Modulus (1024 bit): blahblahblah
Exponent: 65537 (0x10001)

Attributes:

Requested Extensions: X509v3

Basic Constraints: CA:FALSE
X509v3

Key Usage: Digital Signature, Non Repudiation, Key Encipherment

X509v3 Subject Alternative Name:

DNS:kb.example.com, 

DNS:helpdesk.example.com, 

DNS:systems.example.com 

Signature Algorithm: sha1WithRSAEncryption
blahblahblah

So now we've got a shiny new CSR. But, of course, we have to sign it.

Sign the CSR and create a new certificate

Openssl has certificate authority signing functionality, but frankly, I found it wanting a bit more than I was willing to give it (serial number files, etc.). Not that it's difficult: there's a great step-by-step guide to using openssl to sign CSRs at http://www.flatmtn.com/article/setti...e-certificates. Instead, I found it easier to download and install SimpleAuthority. It's a free utility for signing CSRs, and it can be downloaded at http://simpleauthority.com/download.html. It works on Windows, Mac, and linux, though you should note that the linux version requires using the SUN Java package (intructions for setting up here) as well as the unlimited encryption strength cryptology encryption files (see the bottom of the Sun download page, under "other downloads"). When you run simpleauthority the first time, it'll prompt you to create a new user and to create a new CA. There are two things that are especially important in this step:

Make sure you remember the password you create; you'll need it each time you sign a CSR.
Make the CA last for 10 years; we don't need these expiring.

Having created the CA, you need to sign the CSR: From the tools menu, select Import -> Certificate Signing Request. SimpleAuthority will prompt for the .csr filename we created in the previous steps. Select that file and click on Open. When you open the csr, you'll be promptd with the following: The requested dname is "...". SimpleAuthority normally only includes CN, OU (x1), O and C fields in the Subject dname. Do you want to include the extra fields in the certificate? The answer is yes. When you click on Yes, you'll see another dialog box asking for the type of certificate to create. Select SSL Server and increase the certificate validity to 3650 days. There's no reason not to make it 10 years. Make sure you've checked the "include extension requests" box; this tells SimpleAuthority to use the SAN fields from the CSR. When you click on OK, your new certificate will be created. you'll see it listed in the main screen. To export this to a file (and copy back to the server), click on the server name in the left-hand panel and then right-click on the certificate listed at the bottom-right. Select export certificate. When you choose to export the cert, you'll be prompted for the file type. Select PEM format and click on export. Choose a filename, and your pem file will be created.

Install your new certificate

Apache

In apache, all that's required is to edit your ssl.conf file and make sure the SSLCertificateFile points to your new .pem file. It's recommended that you place it in /etc/pki/tls/certs. Then restart apache:

service httpd restart

IIS

In IIS , you've got to convert this file to a .pfx format:

openssl pkcs12 -export -in domainname.pem -inkey /etc/pki/tls/private/domainname.key -out /tmp/domainname.pfx -name "domainname"

The above command will convert the pem file to pfx format, which you can import into IIS: Right click on the web site and select properties. Click on the directory security tab and then on the server certificate button. A wizard will pop up; click next, and then you can select to import a pfx certificate. That should be it.

Note the CA Certificate!

There's a rub in doing this: your CA, as a matter of course, won't be trusted by anyone. This normally isn't a problem, as it's true of self-signed certificates, as well. However, many applications (PrinceXML, for example) that retrieve documents from your web site will fail if they don't trust your CA. You'll want to make sure that you export your CA certificate (Tools -> Export -> CA Certificate) and add it to the trusted CA list for your applications.

Active Directory (AD) Authentication on a Linux Server

2009-03-10T17:22:00.046-05:00

Domain Authentication for Linux

I've updated the instructions here to support RHEL 6. I've streamlined things a bit, too, so I'd head over to this page to get the latest on setting up AD authentication for a Linux server. There are a lot of how-tos surrounding the integration of authentication and authorization in Linux through Active Directory domains. I've found a variety of them helpful, and I've found more to be confusing, rather than helpful. Here I'm hoping to put together a start-to-finish process for using your domain to authenticate and authorize users on your linux box. These instructions are written for Red Hat Enterprise Linux v5 (RHEL from now on). That means the file locations should be the same for CENTOS distros (which I highly recommend as a server OS), but that they might move around a bit for others.

A very small bit of background: this system uses winbind and smb to connect to the domain to authenticate users. Kerberos is used to join the system to the domain. The benefit of this system is that it's secure and it's pretty easy to configure. Coupled with PAM (Pluggable Authentication Modules), it's quite flexible, and it's really pretty cool functionality.

Likewise Open

If this looks too daunting (it's not so hard, I promise!), or if you don't want to mess with Samba, take a look at Likewise Open Source. It's a utility that does just what we're doing in this article, but it does it apart from the OS tools we're using here. I've not used it, but I've heard good things about it. It also looks like it's got some neat admin features that could be really useful. If you've had experience with Likewise, I'd love to hear about it.

And now back to the show

To be clear: this isn't utilizing kerberos except for joining the domain; it's using samba (smb) and winbind (which is a part of samba). A note about a particular assumption here: we're using DNS for name resolution. A lot of instructions out there assume you're using a hosts file for name resolution. That's certainly an option, if you're not in an environment where DNS is practical. All that is to say: we're assuming your computers can find each other without special changes to your hosts file. So the first thing we want to do is edit the relevant configuration files.

Edit the configuration Files

Seems like the easiest thing is to include the two most important config files. Critical changes are in blue, and my comments are in orange. Lots of folks are using "example.com" for sample config files, so I'll go with that as well. Substitute your domain name wherever you see example.com. The short name of the domain, then, is "example".

/etc/krb5.conf

Make sure your file reads as follows. Note the capitalization.

default_realm = EXAMPLE.COM

dns_lookup_realm = true

dns_lookup_kdc = true

ticket_lifetime = 24h

forwardable = yes

 [realms]

EXAMPLE.COM = { 

kdc = adserver.example.com:88 

admin_server = adserver.example.com:749 

default_domain = example.com }

 [domain_realm]

 .example.com = EXAMPLE.COM 

example.com = EXAMPLE.COM

/etc/samba/smb.conf

#================== Global Settings =================== 

[global]  

# workgroup = NT-Domain-Name or Workgroup-Name, eg: MIDEARTH

workgroup = example  

# server string is the equivalent of the NT Description field 

# Which is to say:  this can be whatever you want it to be.

server string = My Linux Box  

# Security mode. Defines in which mode Samba will operate. Possible 

# values are share, user, server, domain and ads. Most people will want 

# user level security. See the Samba-HOWTO-Collection for details.

security = ads  

# This option is important for security. It allows you to restrict 

# connections to machines which are on your local network. The 

# following example restricts access to two C class networks and 

# the "loopback" interface. For more examples of the syntax see 

# the smb.conf man page 

# This can be quite useful, if you've got a limited number of users: 

# it's difficult to hack in if there only are a couple of gates into the city.

; hosts allow = 192.168.1. 192.168.2. 127.

# The next two lines tell the OS what range of UID and GID (user and group ID numbers) 

# to use when creating new accounts.  It doesn't really matter terribly what you use, but 

# most folks keep it in the high range for clarity. 

idmap uid = 10000-20000  

idmap gid = 10000-20000 

# Don't forget to set this next line 

template shell = /bin/bash 

#Telling winbind to use the default domain essentially means there are some situations (like logging in) 

# in which you won't have to specify the domain name. 

# We use it, but there is a pitfall:  domain users don't have to 

# specify the domain name when logging in; if there are local 

# accounts with the same name, serious confusion can ensue. 

winbind use default domain = true [SNIP]  

# DNS Proxy - tells Samba whether or not to try to resolve NetBIOS names

# via DNS nslookups. The default is NO. 

# HEADS-UP:  the following is case-sensitive:  it needs to be all upper-case 

realm = EXAMPLE.COM 

encrypt passwords = yes ; 

guest ok = no ; 

guest account = nobody  

# These scripts are used on a domain controller or stand-alone 

# machine to add or delete corresponding unix accounts ; 

add user script = /usr/sbin/useradd %u ; 

add group script = /usr/sbin/groupadd %g ; 

add machine script = /usr/sbin/adduser -n -g machines -c Machine -d /dev/null -s /bin/false %u ;

delete user script = /usr/sbin/userdel %u ; 

delete user from group script = /usr/sbin/deluser %u %g ; 

delete group script = /usr/sbin/groupdel %g   

#=================== Share Definitions =====================

Note that I've left the settings in the above file in the locations where you find them in a default smb.conf file. Settings that aren't in there by default are grouped at the top. All of these settings, though, are global, so they all can be clumped together, if you so choose.

/etc/nsswitch.conf

# An example Name Service Switch config file. This file should be 

# sorted with the most-used services at the beginning. 

# 

# The entry '[NOTFOUND=return]' means that the search for an 

# entry should stop if the search in the previous entry turned 

# up nothing. Note that if the search failed due to some other reason 

# (like no NIS server responding) then the search continues with the 

# next entry. 

# 

# Legal entries are: 

# 

#       nisplus or nis+         Use NIS+ (NIS version 3) 

#       nis or yp               Use NIS (NIS version 2), also called YP 

#       dns                     Use DNS (Domain Name Service) 

#       files                   Use the local files 

#       db                      Use the local database (.db) files 

#       compat                  Use NIS on compat mode 

#       hesiod                  Use Hesiod for user lookups 

#       [NOTFOUND=return]       Stop searching if not found so far 

#  

# To use db, put the "db" in front of "files" for entries you want to be 

# looked up first in the databases 

# 

# Example: #passwd:    

db files nisplus nis 

#shadow:    db files nisplus nis 

#group:     db files nisplus nis  

passwd: files winbind 

shadow: compat 

group:  files winbind  

#hosts:     db files nisplus nis dns 

hosts:      files dns 

# Example - obey only what nisplus tells us... 

#services:   nisplus [NOTFOUND=return] files 

#networks:   nisplus [NOTFOUND=return] files 

#protocols:  nisplus [NOTFOUND=return] files 

#rpc:        nisplus [NOTFOUND=return] files 

#ethers:     nisplus [NOTFOUND=return] files 

#netmasks:   nisplus [NOTFOUND=return] files  

bootparams: nisplus [NOTFOUND=return] files  

ethers:     db files 

netmasks:   files 

networks:   files dns

protocols:  db files 

rpc:        db files 

services:   db files  

netgroup:   nisplus  

publickey:  nisplus  

automount:  files nisplus 

aliases:    files nisplus

Join the domain

Now we can restart samba to pick up the new settings and join the domain:

service smb restart net ads join -u username

where username is a domain user who has permissions to join a computer to the domain. We should get a response about having joined the domain. Don't worry about errors in updating the DNS; if your server cannot update the DNS, then we don't want it to. Now start winbind:

service winbind start

Test out your settings to make sure you can enumerate the domain groups (I'd use groups instead of users; as a large domain can take a very long time to send over all the users).

wbinfo -g

Now we can edit our PAM configuration:

/etc/pam.d/system-auth

#%PAM-1.0 

# User changes will be destroyed the next time authconfig is run. 

# Note the above statement. If you run authconfig after manually changing this,  

# some of your changes may get dropped. 

auth     required    pam_env.so 

auth     sufficient  pam_unix.so nullok try_first_pass 

auth     requisite   pam_succeed_if.so uid >= 500 quiet 

auth     sufficient  pam_winbind.so use_first_pass 

auth     required    pam_deny.so  

account  required    pam_unix.so 

account  sufficient  pam_succeed_if.so uid < 500 quiet 

account  required    pam_permit.so  

password  sufficient  pam_winbind.so use_authtok 

password required    pam_deny.so  

session  optional    pam_keyinit.so revoke 

session  required    pam_limits.so 

session  [success=1 default=ignore] pam_succeed_if.so service in crond quiet use_uid 

session  required    pam_unix.so

The above is called by most of the other PAM modules, so we should be able to do minimal modifications to other files after having changed this one. We do want the system to create a home directory for people when they log in, so we'll add the following line both to the gdm and sshd files:

session required pam_mkhomedir.so skel=/etc/skel umask=0077

The umask 0077 will give the users full control over their home directory, but no one else will have access. Having added that, we should be able to log in using the username EXAMPLE\username That is, DOMAINNAME\username, to be clear.

Now, since we told winbind to use the default domain, people also should be able to log in using just their domain username. It's probably a good idea, to avoid confusion, to have people use the domain name, too. But there certainly will be situations in which that's not necessary. If you're not able to log in, make sure that your sshd file has calls to system-auth, like in the following example:

#%PAM-1.0 

auth       include      system-auth 

account    required     pam_nologin.so 

account    include      system-auth 

account    sufficient   pam_localuser.so 

password   include      system-auth 

session    optional     pam_keyinit.so force revoke 

session    include      system-auth 

session    required     pam_loginuid.so 

session    required     pam_mkhomedir.so skel=/etc/skel umask=0077

This is the case by default, so all should be fine. Note that this works, too, with the gdm PAM file; that means you can log in to a remote GUI (GDM in this case) on your Linux box with your domain name. Pretty cool.

Restricting Login with Domain Groups

Now that we've set this up, we want to be able to limit the users who can log in to this computer. As it stands right now, any domain user can log in. Usually not the best option (though it may be in your case).
So we'll add a line to the ssh and gdm PAM module configuration files:

account required pam_succeed_if.so user ingroup EXAMPLE\groupname

This line will be the last of the "account" lines. You can stack these, as well as specify users instead of groups, which provides a lot of flexibility. Let's say that we have three groups, linuxusers, webadmins, and domainadmins that we want to be able to log in to our linux server.

Let's say that our domain name is TEST, and let's say that we want, also to allow a user "frank" to be able to log in to our server. We could have a sshd file like this that would allow just that:

#%PAM-1.0 

auth       include      system-auth 

account    required     pam_nologin.so 

account    include      system-auth 

account    sufficient    pam_localuser.so 

account    sufficient    pam_succeed_if.so user = TEST\frank 

account    sufficient    pam_succeed_if.so user ingroup TEST\linuxusers 

account    sufficient    pam_succeed_if.so user ingroup TEST\webadmins1 

account    required     pam_succeed_if.so user ingroup TEST\domainadmins 

password   include      system-auth 

session    optional     pam_keyinit.so force revoke 

session    include      system-auth 

session    required     pam_loginuid.so 

session    required     pam_mkhomedir.so skel=/etc/skel umask=0077

So we've got our user frank allowed (along with all local users on the line above it), and the groups below. Note that the account is "sufficient" until the last group: these are heirarchical, so if the "required" group is first, none of the other groups will be able to log in.

Troubleshooting

One of the most common problems is previous domain joins on the system. One way to take care of that problem is simply to delete the files that are created on the server when joining a domain. Those are (again, on RHEL and CENTOS; other distros have the same files, but in different locations):

/etc/samba/secrets.tdb
/var/cache/samba/*.tdb

remove the above files (perhaps you might make a copy of the secrets.tdb file) and try to join the domain again.

Singing the praises of DekiWiki

2009-03-05T17:24:00.002-06:00

I've been a devotee of MediaWiki for some time, now, and I still have a soft spot in my heart for it. Having said that, however, I've become a real fan of DekiWiki. It's a very full-featured wiki with a ton of extensibility. And the best part: it's released in a community-supported edition, so it's available to everyone without cost. There is a lot that can be said for DekiWiki, and the truth is that I'm too lazy to run through it all. Suffice it to say that it has a ton of features, including extensions, a very flexible scripting language, and--for many the Holy Grail--a very robust ACL system, by which permissions can be set on the individual page level. Here's another bonus: they provide a feature by which MediaWiki installations can be converted to Deki. And I can attest to this: the conversion works well. The commercially-licensed version, to which I suspect we'll move in the near future (I'll post our experience), has a suite of additional features, including a desktop-based toolset that allows for publishing from Outlook and Word, as well as drag-and-drop functionality for organizing the articles. Installation instructions for the GPL'd edition can be found at the MindTouch Deki site. I'll leave it with this: the developers are very active on the MindTouch developer site, very quickly--and thoroughly--addressing problems you might run into. They're a most helpful bunch.

Installing OpenSolaris on a server with a HP Smart Array Controller

2009-02-13T09:26:00.023-06:00

I'm quite new to OpenSolaris; insofar as it's like Linux, I'm pretty comfortable, but there appear to be enough little gotchas to make the learning curve a little steeper than one might like. Take, for instance, the fact that OpenSolaris doesn't ship with the drivers for the almost-ubiquitous HP Smart Array controller. That makes for an installation hiccup that a quick Google search shows many people have found difficult. It's too bad, too: it appears that this has stymied a lot of folks in trying to install the OS. Like most things, the solution is easy, once you know how to do it. These instructions, originally, were for OpenSolaris 2008.11, but they've since been updated to work with 2009.06.

Boot to the Install CD

The first task is to boot to the OpenSolaris Live CD, which is also the install CD. If you haven't downloaded it, yet, you can get it at http://opensolaris.org

Get the HP Smart Array Driver

Download the CPQary3 driver, which includes Solaris drivers for most of the recent (and not so recent) Smart Array controllers. As of this writing, the latest version of these drivers is v2.2.0.

Update: I've tried a few times to get the v2.0 drivers to work on the latest-generation HP servers, and I've had no luck. The v2.2.0 drivers are out, and they support the latest that HP has to offer. I've confirmed that the 2.1.0 drivers work on a BL460 G6 .

So stay away from the 2.0.0 drivers with new HP servers: they can cause some serious heartburn. The HP CPQary drivers can be found here. The /tmp filesystem has a lot of space on the live CD, so it's probably best to save the file there.

I saved off the 1.9.2 drivers here, if that's helpful. As always, it' a better idea to go to the source (HP in this case) for files than a second-hand location, but if you can't locate it at their site, it'll remain available here.

Install the Driver

Now that we've got the drivers, we'll unpack them and go about installing them:

jack@opensolaris:/tmp$ tar -zxf *.gz

jack@opensolaris:/tmp$ ls

CPQary3-2.0.0-solaris10-i386

dbus-D43WuQsGnK

iconf_entries.363

CPQary3-2.0.0-solaris10-i386.tar.gz

dbus-EmWPHCv5Ec

ogl_select471

Just for simplicity's sake, I renamed the directory, so that it was a bit less unwieldy.

jack@opensolaris:/tmp$ mv CPQary3-2.0.0-solaris10-i386 cpqary

jack@opensolaris:/tmp$ cd cpqary

jack@opensolaris:/tmp/cpqary$ ls

CPQary3.144

 CPQary3.pkg

 LICENSE.CPQary3

 RELEASENOTES.CPQary3

CPQary3.iso

 DU

 README.CPQary3

 tools

Note that the OpenSolaris Live CD logs in with the username 'Jack,' which doesn't have much in the way of priviledges. Instead of sudo, use the pfexec script to run the commands with elevated priviledges.

Now here is where some persistent Googling paid off. There's a bug report at the OpenSolaris site (bug #5860) where a developer suggests a step (creating an empty file in the root dir) that makes things all OK.

Note that this step continues to be necessary with OpenSolaris 2009.06 and the v2.1.0 CPQary3 drivers. So here are the rest of the steps:

Create a file on root:

jack@opensolaris:/tmp/cpqary$ pfexec touch /ADD_DRV_IGNORE_ROOT_BASEDIR

Once we've done that, we can install the driver we downloaded:

jack@opensolaris:/tmp/cpqary$ pfexec pkgadd -d ./CPQary3.pkg 

The following packages are available:

1  CPQary3     HP Smart Array Controller Driver
 (i386)

2.0.0,Rev=2008.12.05.01.09

 Select package(s) you wish to process (or 'all' to process
all packages).

(default: all) [?,??,q]:

Processing package instance  from 

HP Smart Array Controller Driver(i386) 2.0.0,Rev=2008.12.05.01.09

Copyright 2008 Hewlett-Packard Development Company, L.P.

## Executing checkinstall script.
Using  as the package base directory.

## Processing package information.

## Processing system information.
11 package pathnames are already properly installed.

## Verifying package dependencies.

## Verifying disk space requirements.
WARNING:
The /usr filesystem has 0 free blocks.

The current installation requires 158 blocks, which includes a required 150 block buffer for open deleted files.

158 more blocks are needed.

WARNING:
The /usr filesystem has 0 free file nodes.

The current installation requires 26 file nodes, which includes a required 25 file node buffer for temporary files.

26 more file nodes are needed.

 Do you want to continue with the installation of  [y,n,?] y 

## Checking for conflicts with packages already installed. 

## Checking for setuid/setgid programs. 

This package contains scripts which will be executed with super-user
permission during the process of installing this package. 

 Do you want to continue with the installation of  [y,n,?] y 

 Installing HP Smart Array Controller Driver as 

## Installing part 1 of 1. 

/kernel/drv/amd64/cpqary3 

/kernel/drv/cpqary3 

/kernel/drv/cpqary3.conf 

/usr/share/man/man7d/cpqary3.7d 

ERROR: attribute verification of  failed
pathname does not exist
[ verifying class  ] 

ERROR: attribute verification of  failed
pathname does not exist
[ verifying class  ]
[ verifying class  ] 

## Executing postinstall script. 

Installation of  partially failed.

Note a couple of things: the defaults are sufficient, and there are errors in the install. Happily, the errors are in copying the man pages, which we'll not need, at least for now (the /usr filesystem is read-only in the live CD). The good news is that the driver now is installed for the Smart Array controller.

Install OpenSolaris

Double-click on the "install OpenSolaris" icon, and you now should be able to see your drives for installation.

Troubleshooting

Reboot loop

If, after installing OpenSolaris as above, you find yourself in a reboot loop, the best thing to do in troubleshooting it is to set a boot option such that OpenSolaris will display text as it's booting, rather than a graphical progress screen. To enable this, type the letter e when presented with the GRUB boot loader menu. This will allow you to edit the boot options. You'll see a list of the steps that are used in booting OpenSolaris. First we want to get rid of the splash screen. Highlight the line that references "splashimage" and press the letter d. That will delete the image that otherwise will cover up the debugging text. Now select the kernel line (it begins with 'kernel$') and type the letter e again. If you're unfamilar with GRUB, you're getting a glimpse into how it works: basically, it's a series of commands that sets up the system and then passes control over to the operating system. It's a great system for dual booting (Windows, unbeknownst to most, uses something similar, if more mysterious). So now we've got a line that, by default, looks more or less like this:

kernel$ /platform/i86pc/kernel/$ISADIR/unix -B $ZFS-BOOTFS,console=graphics

We want to add verbosity and debugging to the boot options, so add -k -v to that line and remove the graphical console, such that it reads like this:

kernel$ /platform/i86pc/kernel/$ISADIR/unix -B $ZFS-BOOTFS -k -v

Hit ENTER, and you'll be returned to the GRUB menu, where you can hit the letter b to boot the system. Now you can see the error that is causing the system not to boot (or to reboot). In many cases, you'll see that the error is "cannot mount root" because there's something wrong with the SCSI controller driver. In this case, try the installation again, using an older version of the CPQary3 drivers.

Enabling Event Logging

HP, for some reason, turned off storage controller event logging in the latest version of the CPQary3 driver. You can read the full HP Advisory here, along with instructions on enabling the logging.

Setting up a VNC remote desktop in Enterprise Linux 4, 5, and 6

2009-02-06T16:30:00.040-06:00

This is something that is terribly useful, and there are lots of how-to articles running around. Unfortunately, I've not found one that was quite complete, especially for someone who is only just getting familiar with Linux (which is precisely when you most need a GUI!). So this is a step-by-step instruction list on getting a remote X Windows session set up on enterprise linux 4, 5, and 6 (RHEL and CENTOS) that can be accessed with VNC viewer software (do a quick search on VNC if you're not familiar with it). In some set-ups, people create a VNCSERVER process that runs all the time. This does allow for persistent sessions (sessions that survive a disconnect), but it's not really what most of us need most of the time. What we're doing here is using xinetd, which will spin up Xvnc on an as-needed basis. Much cooler. Please note: vnc is not a secure protocol. What this means is that any traffic that VNC sends over the wire is *not* encrypted. Do make sure that your use of this doesn't put you at risk for having sensitive data (passwords, for instance) compromised.

Change the RunLevel

Change the runlevel to 3. This will stop GDM (which is what we're using in this example), or whatever your GUI front-end is.

init 3

Enable XDMCP

Since we want to use Gnome, we'll make sure it's the default desktop manager. Edit /etc/sysconfig/desktop and make sure it includes the following line:

DESKTOP="GNOME"

DISPLAYMANAGER=GNOME 

Now activate xdmcp: edit the /etc/gdm/custom.conf file and find the following lines (NOTE: in RHEL 4 and CentOS 4, this is in /etc/X11/gdm/gdm.conf).

[xdmcp]

# Distributions: Ship with this off. It is never a safe thing to leave

# out on the net. Alternatively you can set up /etc/hosts.allow and

# /etc/hosts.deny to only allow say local access.

Enable=false

# Honour indirect queries, we run a chooser for these, and then redirect

# the user to the chosen host. Otherwise we just log the user in locally.

change it to


Enable=true

Install VNC

If you're running a stock EL 5 system, you most likely already have vncserver installed. If you are running EL6, however, you'll need to install it:

yum install tigervnc-server

Create Xinetd startup file

Make sure xinetd is installed. The easiest way is to install it:

yum install xinetd

Create a file called vncserver in /etc/xinetd.d/ with the following content. This will tell xinetd to listen on 5908 and 5910 for a desktop of 800x600 and 1024x768 respectively (I like to have the option of using a lower resolution if necessary).
Note that the service names aren't important; feel free to change them. Likewise the resolution ("geometry") can be changed to fit your needs. The server_args line is what gets passed to the Xvnc application.
Change 'localhost' to the fully qualified domain name of your system.
If you would like a descriptive title on the title bar of the viewer, put that text after the -desktop= piece. Heads-up: the spaces around the equals signs are important. Xinetd won't read this if you omit them.

service vnc10
{

protocol = tcp

socket_type = stream

wait = no

user = nobody

server = usr/bin/Xvnc

server_args = -inetd -query localhost -once -geometry 1024x768 -depth 16 SecurityTypes=None –desktop=<Window_Name_Here>

port = 5910

type = unlisted

}

service vnc8
{

protocol = tcp

socket_type = stream

wait = no

user = nobody

server = /usr/bin/Xvnc

server_args = -inetd -query localhost -once -geometry 800x600 -depth 16 SecurityTypes=None –desktop=<Window_Name_Here>

port =  5908

type = unlisted
}

Reload the xinetd settings with service xinetd restart

Enable the service

Set up the service to start on boot Turn on xinetd

chkconfig xinetd on

Set X Permissions

You have to allow X to display for various users on the ports you've allowed. The best way to do this is to use the Xn.hosts file, where n is the display number you're using. In our case, we're using 8 and 10, so we'll create two files: /etc/X8.hosts and /etc/X10.hosts. They'll contain just one line, that is the fully qualified domain name of the server.

Go back to runlevel 5

init 5

Configure your firewall

We have to allow traffic to come in to your computer on the two ports we configured in the vncserver xinetd file. In our example, we're using TCP ports 5908 and 5910, so we'll add lines to our iptables file to allow that incoming traffic:

vim /etc/sysconfig/iptables

Having opened the firewall file, we'll add the appropriate lines:

-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 5908 -j ACCEPT

-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 5910 -j ACCEPT

These two lines tell the firewall to accept TCP traffic from any source to the ports 5908 and 5910. Save your changes to the file, and then restart the firewall to pick up the additional ports:

service iptables restart

We now should be able to connect to the server using 5908 and 5910. Use the stand-alone vncviewer application to connect. VNC works by specifying ports at the end of the computer domain name (e.g., hostname:10, to connect on port 5910). Once you are connected you should have the usual graphical login screen from gnome.

Set up clipboard functionality

Xvnc doesn’t do copy/paste to the client by default; it needs a helper app to do that. This is called vncconfig.

To set it up so that it runs at login, first log in (this must be done for each user who would use the system).
Click on Applications (SYSTEM in CentOS and RHEL v5) and select Preferences -> More preferences -> Sessions.
Click on the Startup Programs tab and click the Add button.
Type /usr/bin/vncconfig -nowin in the startup command field.

The next time that user logs in, copy and paste will be supported.

Troubleshooting

Connection Closed Unexpectedly or a Black Screen

If, after doing the above, you get a “the connection closed unexpectedly” error when trying to connect, try changing the -query localhost to -query in the file in the /etc/xinetd.d dir. The safest thing, if you run into this problem (or a black screen) is to replace ‘localhost’ with the output of hostname –f on the server. It could also indicate a typo in the vncserver file in /etc/xinetd.d, so check that file closely.

Client is not authorized to connect error

If you see something like the error below, it indicates that you need to create the Xn.hosts file as referenced above. Create the file with the number indicated in the display: piece of the error. In the case below, it'd be X0.hosts file that needs to be created. You'll have to log out and back in for this change to take place (or do an init 3, init 5).

Xlib: connection to ":0.0" refused by serverXlib: server.example.com is not authorized to connect to serverError: can't open display: :0

Blank Screen after Login

If you run into a blank (usually background-colored) screen after entering your username and password, this indicates some Gnome (XWindows) session data has gotten corrupted. The easiest way to test this is to try logging in as another user. If you can do so without getting this error, here's how to fix it:

Delete all files and directories beloning to the affected user in /tmp. You can focus that a little more by first deleting any file/directories that appear with the name "gnome" or "X" in them. After doing this, try logging in again. It probably will work.
If #1 doesn't get things going, you may also have to delete the following files and directories from the user's home directory (typically /home/username):

.esd_auth	.gconfd	.gnome2_private	.nautilus
.dmrc	.gconf	.gnome-desktop	.metacity
.fonts.cache*	.gnome	.gnome2
.ICEauthority	.gtkrc*

Gray screen only

A gray screen with no login prompt probably means you forgot to edit the desktop file at the top. It also could be an error in the gdm.conf file. Take a look at the gdm.conf (or custom.conf) file that you edited at the top and make sure the remote (or xdmcp) greeter in the [daemon] section is blank or commented out.

DBus Problems - DROM Access Denied

If you receive a message stating something like this:

A security policy in place prevents this sender from sending
this message to this recipient, see message bus configuration file
(rejected message had interface “org.freedesktop.Hal.Device.Volume”
member “Mount” error name “(unset)” destination “org.freedesktop.Hal”)

Then you've run into this problem. The problem is corrected by adding the following lines to the end of /etc/dbus-1/system.d/hal.conf:

<policy group=" users=">

<allow send_interface="org.freedesktop.Hal.Device.SystemPowerManagement" />

<allow send_interface="org.freedesktop.Hal.Device.VideoAdapterPM" />

<allow send_interface="org.freedesktop.Hal.Device.LaptopPanel" />

<allow send_interface="org.freedesktop.Hal.Device.Volume" />

<allow send_interface="org.freedesktop.Hal.Device.Volume.Crypto" />

</policy>

SQL Server Bulk Insert Using a Format File to Read Data that also Includes a Comma in the String

2008-08-29T15:50:00.013-05:00

There are a lot of resources out there on the net about using SQL Server Bulk Insert, and there are a lot of technical discussions about the inner details of Format Files. The problem is that there aren't many concrete examples of a very common need: importing a CSV (comma-separated) file where a quoted string also includes a comma. Here's an example of what some data might look like:

ID,Name,Phone 

100983,"Jones, Frank",555-1212

118928,"Smith, Joe",555-1313

115454,"Franklin, Alibaster",555-1414

Using a bulk insert statment without a format file would give us data that would look like this:

ID	Name	Phone
100983	"Jones	Frank"	555-1212
118928	"Smith	Joe"	555-1313
115454	"Franklin	Alibaster"	555-1414

When in reality, we want the data to look like this:

100983	Jones, Frank	555-1212
118928	Smith, Joe	555-1313
115454	Franklin, Alibaster	555-1414

The format file is your friend! Basically, you'll create a format file that looks like this:

0

3

SQLCHAR 0 6 ",\"" 1 empid SQL_Latin1_General_Cp437_BIN
SQLCHAR 0 50 "\","

name SQL_Latin1_General_Cp437_BIN
SQLCHAR 0 10 "\n"

phone SQL_Latin1_General_Cp437_BIN

Here's how the above breaks down: 8.0 is the version. That's constant. For SQL Server 2005, anyway. :) 3 tells the Bulk Insert statement how many fields there are in each row. Then we have the three fields listed: 1, 2, and 3 are the field numbers. 6, 50, and 10 are the field sizes. You'll need these to match the data types that are defined in your table. Now we get to our field delimiters. In a format file, delimiters are specified in double quotes. If a double quote is a part of a delimiter, then it needs to be escaped with a backslash:

",\"" (note the double double quotes) means, then, that there will be a comma followed by a double quote at the end of the first field. This is nice in that it does two things for us: first, it delimits the field, but it also will strip the first quote from the data as it's inserted.
"\"," means that there will be a quote followed by a comma at the end of the second field. In this way, we've skipped the comma that is in the data, and we've stripped the second quote from the data.
"\n" means that the last field will be delimited by an end-of-line character.

SQL_Latin1_General_Cp437_BIN has to do with the database collating setting, and many people simply leave that out of their format file without apparent ill effect. Now you can use a bulk insert statment like this:

bulk INSERT temptable FROM 'csvfilename' WITH (FIRSTROW = 2, FORMATFILE='formatfilename')

This will insert the CSV file into table TEMPTABLE, starting at the second row (we don't want to import the headers), using your new format file.

WebLogic Case Sensitivity

2008-07-10T14:37:00.005-05:00

Prior to v9.0, Weblogic was case-insensitive in Windows, but starting with v9, the default is, like Apache, to be case sensitive. A web search on changing that behavior isn't very helpful, and the setting is buried a bit, so I thought I'd post the instructions on changing that setting. The first step is to run the weblogic admin console. The default URL, once that has been started, is to browse to http://servername:9999/console. You'll be prompted for the username/password that you configured when installing weblogic. Once there, click on Domain-> Security-> Advanced. There you will see a "Web App Files Case Insensitive:" setting. By default, this is set to "false". Change it to "os", click on save. Once it's been saved, the changes have to be Activated. Click on the green Activate Changes button on the left-hand side of the screen.

You'll have to reboot the admin console, as well as your standard weblogic web services, for this change to take effect.

Persistent Problem with MS Update

2008-06-17T16:45:00.003-05:00

Have you ever gotten a Windows system that simply won't update? That is, it'll find all the updates it needs from Microsoft, but the updates won't do anything but fail. It's a bummer, because the normal reboot simply won't fix the problem. It turns out there is a solution. The problem appears to come from XP SP3, beauty, that. In any case, this should fix the problem: Run: REGSVR32 WUAPI.DLL REGSVR32 WUAUENG1.DLL REGSVR32 ATL.DLL REGSVR32 WUPS2.DLL REGSVR32 WUCLTUI.DLL REGSVR32 WUPS.DLL REGSVR32 WUWEB.DLL REGSVR32 WUAUENG.DLL

Windows Vista Look and Feel in XP

2008-06-17T16:14:00.002-05:00

If you're interested in making your XP system look like Vista, there are a ton of resources out there. Why another one? Because I want to keep it simple. I found two resources that gave me the whiz-bang look and functionality without fiddling too much with lots of other apps. In short, I used what must be the gold standard, Vista Transformation Pack, along with TrueTransparency and the Microsoft Zune XP theme. With these three packages, I got more than I needed, and they're all free. I ended up unselecting the toolbar and start button transformations from the vista transformation pack; I don't care for the tool bar, and the Start Button just wasn't quite where I think it should be: you can't right-click on the icons, and there's just a bit missing. Still, it makes for a great user experience, without a lot of the Vista negatives.

Desktop Search for Windows x64

2008-06-11T10:37:00.010-05:00

Windows released version 4 of its desktop search recently, and I must say that it (mostly) works well. With the Vista desktop, one simply can begin typing a search string at the start menu, and Windows will present the search results there as you type.

The problem: UNC indexing isn't apparently supported in the 64 bit environments, at least not with Server 2008, and the network shares I want to index are stored on Windows Server 2000 boxes: those won't run the new indexing tool.

So what to do?

Of course: Google Desktop. It's fast, it supports network share indexing, and it's got a very intuitive interface.

But here's the problem: That says: "Google Desktop is not currently compatible with your operating system. It requires a 32-bit version of Windows..."

Ugh. Leaving out the rant, there's a happy solution. Before going to the solution that works in the corporate environment, Copernic Desktop Search is worth looking at for those at home. Unfortunately, work is really where I need this, and Copernic requires a fee for corporate users.

So we're back to Google. It turns out that Google Desktop version 5.1.706.29690 *does* in fact work with 64 bit versions of Windows. So all is happy in search land.

Filehippo can get you that version (and others) of Google Desktop here.

Note that later versions (like 5.7.802.22438) also will install on Win x64, but I've not been able to get them to index files, especially network shares. So if you've installed a later version and constantly see "Crawl not yet started", try an earlier version.

[Edit]
Google has heard the call for 64-bit support, and responded with this post:http://desktop.google.com/support/bin/answer.py?hl=en&answer=25631
to wit:

If you'd like to install and use Google Desktop on 64-bit Windows in an unsupported capacity, you can do so by using the /force flag when using the command line to install Google Desktop.
Command line argument: googledesktopsetup.exe /force

So, it's unsupported, and they note that certain features may not work. So it might be worth a try. For me, I'm content with the functionality I've got. Anyone had any experience using this method?

PeopleSoft Upgrade Woes

2008-04-22T13:59:00.005-05:00

When upgrading a PeopleSoft database from 8.47 to 8.49, we ran into a curious problem, receiving this error:

SQL Error. Error Position: 14 Return: 6550 - ORA-06550: line 1, column 15: PLS-00103: Encountered the symbol "TABLE" when expecting one of the following: := . ( @ % ; 

It turns out that it was a simple problem, but it wasn't at all obvious from the error: the DDLORA.DMS script that ran during the initial part of the upgrade was old; for whatever reason, it hadn't gotten updated by the install process. This is what was happening: Datamover was importing the tables, and the first succeeded. Then: it went to compute statistics. That's where things went wrong. Here's why: the ddlora.dms script defines not only default tablespace parameters, but also the method by which DataMover will compute statistics. Pre-8.48 PeopleSoft did it using the 'analyze table' method, while 8.49 uses the dbms stats method. With the old statistics parameters in the PSDDLMODEL table, the compute stats call failed, and so did the upgrade.

To fix this problem, we installed PeopleTools 8.49 again on an empty PS Home, and then we copied the new script over.

404 (Not Found) Error When clicking on MySite "My Profile" tab in SharePoint

2008-04-15T14:59:00.004-05:00

This error is vexing, though there's a simple solution documented in MS kb924399. I've summarized the solution below with some assumptions about settings that folks normally use. In short, the problem is that there isn't a site collection set up using the appropriate template in the MySite web application. We'll correct that in a few steps. Note: it's entirely possible--even likely--that you've already got a site collection created at the root of the mysite web application. If this is a single personal site, that's part of your problem. This site will need to be backed up and deleted before you can go further, assuming that all of your other personal sites are using a different path. First, we need to create the managed path that SharePoint will use for this site collection.

This can be anything, but most people will have created their sites with the default root ( / ) site collection. So we'll go with that for our purposes.
To do this, click on Application Mangement -> Define Managed Paths. Make sure, once the defined managed paths screen comes up, that you've got the correct MySite web application selected in the top-right. Go ahead and create the managed path at the desired location.

Next, we'll want to create the site collection using the correct template.

Click on Application Management -> Create Site Collection to bring up the site collection screen. Make sure you've got the correct web application selected on the left, and then select the managed path you created in the step above for the URL.
Give the site collection a title, like 'Personal Sites', and -- here's the money part -- select the "My Site Host" template from the Enterprise template tab.
Fill in the site administrators fields appropriately and click on OK.

After doing these steps, the My Profile tab should work again.

Simplifying SharePoint Structure with Site Collections

2008-04-10T08:40:00.005-05:00

Site Collections in SharePoint 2007 are the "pieces" of a web application. Personal sites (my site), for instance, are each a site collection. There are a couple of real benefits, too, for using many site collections--instead of multiple web applications--to form the bulk of your SharePoint implementation. SharePoint Maintenance On the whole, it's simpler to have few IIS web sites (and few web applications) and many site collections. This provides for fewer urls (and thus fewer IIS sites) and fewer ACLs (permissions) to maintain. The permissions piece, in particular, can become a real bear in a large SharePoint installation, as each piece of a web application and all of the pieces of all of its site collections can all have different permissions assigned to them. Using site collections as the primary content-delivery piece can simplify the permissions considerably. Backups/Restore Backups in SharePoint are a tricky business, partly because Microsoft gives you many ways to perform backups and recovery. The simplest solution for backing up your SharePoint installation is to do a farm-level backup, which gives you the option of restoring individual web applications. The downside to using lots of web applications, as most SharePoint administrators quickly discover, is that you end up with a multitude of IIS web sites. To create subdirectories within these sites, administrators create site collections within them, giving a pretty complex structure. Moreover, when you perform a web application backup, you have to restore the web application, which includes all of the site collections contained within it. That complicates the restore process quite a bit. Some implementations (particularly very large SharePoint sites) will benefit from the above scenario, but most people can get all the functionality and simpler administration by using site collections as the primary structural unit. Because you can backup and restore individual site collections, it allows you a lot more flexibility and granularity in your backup and restore strategy. Moreover, it happens from time to time that a web application configuration become corrupt, such that, for instance, the my sites functionality no longer works. If you have backed up the individual site collections from that web application, it is a trivial task to create a new web application and import the component site collections into it. Backing up site collections, however, does bring up a problem: because stsadm (the admin tool used to backup site collections) requires a site collection url as a command line parameter, there's not an out-of-the-box way to backup all site collections in one fell swoop. Happily, this problem has been recognized by many competent scripting folks. The script we use is a modified version of the one posted by Mauro Cardarelli on his blog. It runs through all of the site collections in a given url and backs them all up individually. Note that, should you use this script, it was written for SharePoint 2003, and as a result, the path to the stsadm executable needs to be updated from C:\Program Files\Common Files\Microsoft Shared\web server extensions\60\BIN\stsadm to C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN\stsadm.

401 Error when trying to create MySite (personal site) in SharePoint 2007

2008-03-28T08:43:00.008-05:00

It's a common enough problem that you can find untold numbers of questions posted to forums and blogs: why am I getting a 401 denied error when trying to get to/create a mysite personal site in SharePoint? The problem usually is manifest by first being presented with a username/password prompt three times, followed by a 401 error. Interestingly, in Firefox, it appears that sometimes you just get unending username/password prompts. Why this is happening: who knows? It's often possible to track down the problem, usually related to application pool permissions or some rogue setting in SharePoint. Even if you do find the problem, though, enough settings in SharePoint are reasonably inaccessible after the initial setup that finding your way to correcting it can take a very, very long, frustrating time. Our solution has been simply to create a new default shared services provider (SSP) and associate all of the web applications, including the existing mysites, to the new SSP. The Shared Services Provider in SharePoint is the glue that holds everything together. It manages, in particular, what happens when you click on the "mysite" link. So when that link no longer functions properly, one way to fix it is to set up a new SSP using a new content database and new IIS web site. In this way, we can eliminate a lot of errors without having to do a lot of painful troubleshooting. We'll go through those steps now. Note that these steps are for SharePoint 2007; they may work on 2003 as well, but I've no experience with it, so I make no promises as to its applicability. Create the New Shared Services Provider Open SharePoint Central Administration and click on the Shared Services Administration link on the left-hand side of the screen. You should see something like the screen shot below. To create a new Shared Services Provider, click on the New SSP link. You'll want to create a new web application to host this, on a new port. Also, specify a new SSP Database, ensuring that any erroneous settings don't get migrated. Make sure that you use your existing MySite web application (in this example, named "Sharepoint - Personal") for the My Site Location in setting up this new SSP. Once you've created your new SSP, you can set it as the default (using the Change Default SSP link). Once you've done that, use the Change Associations link to associate all of your existing web applications to the new SSP. Having done that, you're probably good to go. You might, just to be safe, double-check your My Site settings in the new Shared Services site. Check your My Site Settings On the left-hand menu, under Shared Services Administration, click on the title of your new SSP. This will open the SSP home page, like below. Click on the My Site Settings link and make sure that the Personal Site Location field matches what you had set up previously. Having done that, you're done! Delete any erroneously-created My Sites Unless you're not: if you discover later that the personal site location field is incorrect, you may end up with users creating new sites when they already had one. This is disconcerting, but it's easily fixed. To delete a user's My Site, simply click on the Delete Site Collection link in the Application Tab of the Central Administration page. There you can select the offending site collection from the My Site web application. This will delete the My Site without problem. However, you still need to change the My Site settings in the SSP, as above. And: having done so, issue a iisreset /noforce on the front-end web servers. Unless you do this, the user runs the risk of being directed, even still, to the now-nonexistent My Site. This gives them a 404 (not found) error. After the iisreset, they should be directed, again, to their original my site.

Value cannot be null error When Trying to View a SharePoint Site

2008-03-26T14:15:00.006-05:00

We ran into a problem after restoring a SharePoint 2007 farm that stumped us for awhile. Namely, the MySites web application wouldn't come up; it had restored successfully, but we'd get a generic error message when we'd try to browse to it. Once we had turned on detailed error reporting, we saw the following error code:

Value cannot be null.
Parameter name: serverContext 
   at Microsoft.Office.Server.UserProfiles.UserProfileManager..ctor(ServerContext serverContext, Boolean IgnoreUserPrivacy, Boolean backwardCompatible)
   at Microsoft.Office.Server.UserProfiles.UserProfileManager..ctor(ServerContext serverContext, Boolean IgnoreUserPrivacy)
   at Microsoft.Office.Server.UserProfiles.ProfileLoader.EnsureUserProfile()
   at Microsoft.Office.Server.UserProfiles.ProfileLoader.GetUserProfile()
   at Microsoft.SharePoint.Portal.WebControls.CreatePersonalSpace.Page_Load(Object sender, EventArgs args)
   at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)
   at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
   at System.Web.UI.Control.OnLoad(EventArgs e)
   at Microsoft.SharePoint.Portal.PageBase.OnLoad(EventArgs e)
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint,Boolean includeStagesAfterAsyncPoint)   

Now this made no sense whatsoever; what's null? The application looked like it was set up correctly, and it had been working before we did the backup and restore (it was a test run of our DR process). Unfortunately, too, all I could find on the web that referenced the error was information about coding tasks, which isn't what I'm doing at all. Finally, we fixed it: Alternate Access Mappings. We were using SSL to access the site (through a netscaler load balancer), but post-restore, SharePoint only had http:// in its configuration. After re-adding https:// as a public URL for the web application, the error goes away.

Configuring SharePoint 2007 to Display Errors

2008-03-26T11:00:00.008-05:00

By default (and this is a good thing), SharePoint displays a simple generic "an error has occurred" message when a problem arises. In troubleshooting these problems, it's useful to turn on more descriptive error messages. The first thing to realize in any discussion of detailed error messages on the web is that it's generally not a good thing to keep your site configured to display details of the errors that are generated; it can reveal much about your infrastructure and security that is best kept secret. So: when you make these changes, be sure to unmake them later. Locating Web.config Configuring SharePoint to display a details (including a stack trace) of its errors involves two simple changes to the web.config file that is stored in the root of the affected web site. Hopefully, in setting SharePoint up, you opted to use meaningful names for the web sites. If, however, that didn't happen, call up the Web Application List in SharePoint's application management tab. You'll see a list of the sites (name) and their respective URLs. Those names correspond to the site names in IIS. Of course, SharePoint doesn't care where that document root is, so if you don't already know where the document root is, open up IIS manager and view the web site's properties. Click on the Home Directory tab, and you'll see the path to use. web.config will be in that directory. Modifying Web.config Having located web.config, open it in a text editor, and find the following line:

Change the above setting to Off. This tells the web app server to display errors as they occur, instead of the generic SharePoint-defined custom error of "An Error occurred." Likewise, find

<SafeMode MaxControls="200" Callstack="false"

Change the false above to true. This tells the web app server to display the error stack when an error occurs. When you save your changes, you should be set, and your SharePoint errors now will display as they occur.

Backing up and Restoring SharePoint 2007 Sites and Site Collections

2008-03-25T20:36:00.010-05:00

Restoring SharePoint 2007 sites and web applications from a backup can be quite a headache; SharePoint is famously tight-lipped when it comes to error messages. This is a rundown of some of the issues we've run into in restoring from backups.

--

Restore Errors

DCOM Permissions

If your restore fails with a log referencing "UnauthorizedAccessException" referencing a COM class with a long CLSID and error number 80070005, you've got a COM Config permissions problem. This error is accompanied by a system event log like the one below.

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{3D42CCB1-4665-4620-92A3-478F47389230}
to the user domain\databaseconnectuser SID (S-1-5-21-2142909598-1293495619-134157935-85307). This security permission can be modified using the Component Services administrative tool.

As the event log error indicates, the fix for this is to use the Component Services tool (dcomcnfg.exe) to give Local Activation permissions to the user listed. Here's the rub: you don't at this time know which COM class to modify. To figure that out, search in the registry for the CLSID as referenced in the event log and restore log.

The registry search will give you the name of the com class. Unless it doesn't: a common class to have this problem is OSearch. If, when you search the registry, you see "Microsoft Office SharePoint Server Search Gathering Manger", the name you'll look for in the component services tool is OSearch.

By the way, you're looking in the DCOM Config tree in Component Services, as in this screen shot.

Right-click on the appropriate class and select Properties. Then click on the Security tab and edit the Launch and Activation Permissions.

Add the user that is mentioned in the event log, and give that user local launch and local activation permissions.

Finally, restart IIS with a iisreset /noforce. That should take care of this error.

Web Site Is In Use Restore Error

Especially if you've set up multiple SharePoint sites using host headers and SSL, you'll likely end up with the following error when trying to restore the web application:

ArgumentException: The IIS Web Site you have selected is in use by SharePoint.
You must select another port or hostname.

The issue is that SharePoint, in restoring the IIS sites, doesn't restore the host headers, as well. Hard to figure out why, but there you have it. Moreover, this error isn't a IIS error, which is to say that, even if you fix the alleged conflict, SharePoint still will error out on the restore, because it still thinks there's a problem.

So you have to do one of two things: customize the settings when you restore the web applications (to use different port numbers), or restore them one at a time, configuring each after the individual restore to use host headers. In either case, to end up with the original URLs, you'll have to do some fiddling after the restore is complete.

In short: you want to restore the web applications, such that you've got the content back. Having done that, you can re-create the web sites, using the Extend an existing Web application component of SharePoint's Application Management.

Extend the newly-restored web application to a new IIS web site, using the appropriate port number and URL. Once you've extended it, you can go back and "Remove SharePoint from IIS Web site." Using the remove function, you can delete the unwanted site, leaving only the desired URL.

Rebuilding RPMs in RHEL

2008-03-16T15:29:00.004-05:00

How to rebuild a RPM from source Reconfiguring and rebuilding RPMs can be immensely useful, particularly when, as in the case of AMANDA backup software, you need to make a change that is used over and over again on a lot of clients. The steps are pretty straightforward. In short: To tweak an existing package

rpm -i .src.rpm

This puts all sources into /usr/src/redhat/SOURCES/ and the .spec file into /usr/src/redhat/SPECS/ which you can change as required To change the compile settings Edit the /usr/src/redhat/SPECS/.spec file to meet your needs To build the binary Then run the following command to build the RPM:

rpm -bs /usr/src/redhat/SPECS/.spec

rebuilds the RPM from the modified source. -- How to rebuild the AMANDA source to create new install RPMs This is an important thing to do: AMANDA by default picks random high tcp ports on which to communicate with the clients. This is a problem when we cross subnets, in particular, since the firewall needs to know which ports are needed. So we recompile the source to include the --withtcpportrange= and --withudpportrange= switches. This will limit AMANDA to the appropriate port ranges. This is from http://wiki.centos.org/HowTos/AmandaBackups 1. Download the amanda-xxxxx.src.rpm file. 2. Install the source rpm: rpm -i amanda-2xxxxx.src.rpm. This will extract the contents into your rpm directory (if you're doing it as root, it'll be /usr/src/redhat/.) 3. Edit the SPECS/amanda.spec file to reflect the appropriate changes. It's a good idea to changed the Release: tag to indicate you've made changes. In addition to just helping you keep track of what you've changed from the default, this will help keep the package from being updated automatically when you patch the system. To set up AMANDA to use specific ports that we can open up on the firewall, we want to add the following to the ./configure command. Don't forget to put the trailing back-slashes at the end of the lines.

--with-tcpportrange=50000,50100 \
 --with-udpportrange=700,710

4. Rebuild the rpms:

rpmbuild -bb --define "build_rhel5 1" /amanda.spec

or for RHEL4,

rpmbuild -bb --define "build_rhel4 1" /amanda.spec

Using 'Alternatives' in Linux to use a different Java package

2008-03-15T12:35:00.024-05:00

Installing Sun Java alongside the default GNU java using the 'alternatives' system.
This post shows how to install Sun's java implementation alongside the Linux default GNU java.

Installing Sun Java alongside the default GNU java
Sun's java isn't installed by default on RedHat systems. This is because Sun hasn't licensed it for RedHat's distribution. Instead, Linux ships with an open source alternative, GNU Java. It's based on Sun's Java implementation, and--in part because of that--it's always a version or two behind Sun. In all, it's a very good implementation, but sometimes--as in the case with DSpace--it's necessary to use Sun's Java, instead.
We don't want to remove the Gnu Java; we'll install Sun's alongside it.

Download the files

The first file we need is Sun's java implementation. In this example, we'll be referencing JDK 1.6.0 release 4, but the steps are the same for all versions.

Links to Sun's most recent download packages can be found here.

The second file is a compatibility package from www.jpackage.org. This simply creates a bunch of symbolic links to bring Sun's directory locations into compliance with the GNU system.
We'll be referencing java-1.6.0-sun-compat-1.6.0.04-1jpp.i586.rpm, which works with the version of Sun's Java discussed above (1.6.0_4). Whichever version you're using, you'll need to visit the jpackage web site to download it.
JPackage has managed, somehow to make their downloads almost as inscrutable as Sun's. Their instructions page, though, has good pointers to all the correct downloads.
If you run into problems either finding the correct sun-compat package, or if you're running a distro (such as 64-bit RHEL) for which JPackage hasn't created a sun-compat package, you're not out of luck. We'll also look at how to configure alternatives manually, at least for basic java usage.

Install the Packages

Install Sun Java

Sun packages its distributions in a self-extracting binary file. Simply execute the .bin file from the command line:

./jdk-6u4-linux-i586-rpm.bin

This will take awhile. When it's finished, Sun's Java will be installed.
You can see, however, that the GNU java still is active by typing the following command

java -version

Install Sun Campatibility

java-1.6.0-sun-compat-1.6.0.04-1jpp.i586.rpm is signed by jpackage.org, so the easiest way to install the package is to import the jpackage.org key:

rpm --import http://jpackage.org/jpackage.asc

If you don't want to do this (and thus trust all Jpackage.org rpm packages), you'll need to use RPM instead of yum to install it.
Then, simply install the package:

yum install java-1.6.0-sun-compat-1.6.0.04-1jpp.i586.rpm

Configure Alternatives manually

If you've not found the correct JPackage sun-compat package, or if one isn't available, it's still quite possible to use the alternatives system to manage your java versions.
Alternatives is configured at the command line:
alternatives --install

alternatives --config
alternatives --remove

So, to get straight to the meat of the matter:

alternatives --install /usr/bin/java java /usr/java/jdk1.6.0_11/bin/java 120 \
--slave /usr/bin/keytool keytool /usr/java/jdk1.6.0_11/bin/keytool \
--slave /usr/bin/rmiregistry rmiregistry /usr/java/jdk1.6.0_11/bin/rmiregistry

alternatives --install /usr/bin/javac javac /usr/java/jdk1.6.0_11/bin/javac 120 \
   --slave /usr/bin/jar  jar  /usr/java/jdk1.6.0_11/bin/jar \
   --slave /usr/bin/rmic rmic /usr/java/jdk1.6.0_11/bin/rmic

The above will create two entries in the alternatives symlink config system, one for java (with some "slave" symlinks for dependent apps), and one for javac (likewise with slave symlinks).
By and large, the above should be what's necessary to run java.

Check the active Java Version

Now we need to verify that Sun's java is the default (using the java -version command):

$ java -version
java version "1.6.0_04"
Java(TM) SE Runtime Environment (build 1.6.0_04-b12)
Java HotSpot(TM) Server VM (build 10.0-b19, mixed mode)

Above, we see that Sun's Java v 1.6.0 r4 is the default java. If, however, we see something like the following,

# java -version
java version "1.4.2"
gij (GNU libgcj) version 4.1.2 20070626 (Red Hat 4.1.2-13)

Copyright (C) 2006 Free Software Foundation, Inc.
This is free software; see the source for copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

Then we have to use the alternatives program to set the correct default. Alternatives simply is a system that manages standard symbolic links, allowing you to select one or another alternative program to run for any given command. In our case, that's java.
As root, simply run the following command

alternatives --config java

you'll see something like the following:

# alternatives --config java

There are 2 programs which provide 'java'.

Selection Command
-----------------------------------------------
+ 1 /usr/lib/jvm/jre-1.4.2-gcj/bin/java
* 2 /usr/lib/jvm/jre-1.6.0-sun/bin/java

Enter to keep the current selection[+], or type selection number:

The + sign next to the first line indicates that the GNU Compiler for Java (GCJ) is the default. Simply type the number 2 and press enter to make the Sun Java distro be the default.

Using SSL with a Wildcard Certificate on Multiple Apache Sites

2008-03-13T21:04:00.011-05:00

It is possible to set up multiple virtual SSL-encrypted hosts (sites) in apache, but did you know it's possible for them all to use port 443 and the same IP address? Wildcard certificates help make it happen.
Setting up SSL virtual hosts in linux (specifically RHEL and CentOS, but the steps are similar in other distros) isn't hard, and--to correct a common misperception--it is possible to have multiple SSL virtual hosts running under apache using host headers. To be clear: you don't have to use separate IP addresses or different port numbers in order to have multiple SSL sites under apache.
In short, the process involves creating multiple SSL virtual hosts, each with its own host header (ServerName in apache parlance), and adding the same wildcard SSL certificate to each of the sites. There are a variety of ways to create a certificate, and you can see many of them discussed with a simple Google search. In our case, we'll use genkey from crypto-utils. Feel free to use any utility you'd like

Install Crypto-Utils

Crypto-utils is a package that contains a lot of programs for dealing with certificates and keys; it makes things a lot easier.

yum install crypto-utils

Generate the new key

Then simply run the genkey application:

genkey --days 3650 *.servername.com
You'll notice in the above example we set it to last for 10 years (why should it expire?) and contain the server name servername.com. Of course, you'd want to put the correct server name there.
The program will prompt you for some details; note that you'll not want to have the private key encrypted; that requires a password when you start apache, which isn't terribly convenient.
Genkey will create a file called *.servername.cert and *.servername.key in /etc/pki/tls/certs and /etc/pki/tls/private, respectively. These directories, I believe, are specific to RHEL (and CENTOS) 5; EL 4 and other distros use different directories. You'll want to rename these files when the process is complete: genkey creates the cert and key with the name you typed in, which means it'll be a filename with a * in it. That's no good, so when it's done, go to those directories and change the filename to wildcard.servername.cert and wildcard.servername.key.
When you've done that, your certificate is complete, and you're ready to set up your sites.
As an aside: it's not necessary that you use self-signed certificates. In fact, if you're setting this up for a production environment, you will want to use a commercially-provided certificate from a company such as GeoTrust, Verisign, or any of the myriad of certificate companies out there.

Edit ssl.conf

Now we add our virtual hosts (sites). Again, this directory location may be different for your distro.

vim /etc/httpd/conf.d/ssl.conf
If we were just installing this certificate for the default site, we'd just change the SSLCertificateFile and SSLCertificateKeyFile to point to the new files created in /etc/pki/tls/certs and /etc/pki/tls/private respectively.
But because we're creating (or adding certificates to) two new sites we'll have to change the SSLCertificateFile and SSLCertificateKeyFile not only in the default site, but add our additional sites as well. The following will create two additional sites called "testssl1" and "testssl2." They'll go right after the default <VirtualHost> </VirtualHost> section

NameVirtualHost *
<VirtualHost * >
SSLEngine on SSLProtocol all -SSLv2
SSLCipherSuite ALL:!ADH:!EXPORT:!SSLv2:RC4+RSA:+HIGH:+MEDIUM:+LOW
ServerAdmin webmaster@example.com
DocumentRoot /var/www/html/testssl1
ServerName testssl1.example.com
ErrorLog /var/log/httpd/testssl_error_log
CustomLog /var/log/httpd/testssl_access_log common
SSLCertificateFile /etc/pki/tls/certs/wildcard.example.com.cert
SSLCertificateKeyFile /etc/pki/tls/private/wildcard.example.com.key
</virtualhost>

<virtualhost *>
SSLEngine on SSLProtocol all -SSLv2
SSLCipherSuite ALL:!ADH:!EXPORT:!SSLv2:RC4+RSA:+HIGH:+MEDIUM:+LOW
ServerAdmin webmaster@example.com
DocumentRoot /var/www/html/testssl2
ServerName testssl2.example.com

ErrorLog /var/log/httpd/testssl_error_log
CustomLog /var/log/httpd/testssl_access_log common
SSLCertificateFile /etc/pki/tls/certs/wildcard.example.com.cert
SSLCertificateKeyFile /etc/pki/tls/private/wildcard.example.com.key
</virtualhost>

If you'd prefer simply to replace the default :443 site, simply comment out the entire section between the first <VirtualHost> </VirtualHost> section.
Note that there's a lot that can go wrong with the settings in this file, but, unlike using wildcard certificates, there is--happily--a lot of information on the web about troubleshooting this file.

Restart Apache

service httpd restart

Multiple SSL Sites on a Single IIS Server Using Host Headers

2008-03-12T22:07:00.025-05:00

Wildcard SSL certificates can allow you to publish multiple IIS web sites--all using SSL on port 443--that are accessible by host headers. That is, if you're running on IIS 6 or above.

So you read about these things, wildcard certificates, but mostly you read about how to buy them. It is possible (and easy) to create one for yourself, however, so long as you don't mind it not being trusted by folks' browsers. There are some really good uses for these, not least in a test environment in which you don't care about the trust.
Production environments need them, too, sometimes, such as when you're using a network appliance like a Netscaler or F5 load balancer. Those devices allow you to point multiple URLs (cnames, really) to a single (or multiple) server(s). Very handy.
In our case, we have several web sites that we'd like to secure using SSL. Problem is: they're all on a single server. There are several options for dealing with such a case: assign multiple IP addresses to the server, use different SSL ports for each IIS site, or (definitely the coolest option) use a wildcard certificate on the server to allow IIS to decipher the http host header. This option, by the way, is a new feature with IIS 6, so if there's anyone out there still using IIS v5, this is another reason to upgrade.

Background
A little background on the problem might be in order: a SSL certificate is used to encrypt the http data. Normally, in setting up a IIS web site, we differentiate that site from all the others by assigning unique host headers to each site. IIS can use those host headers to determine to which site to route any given http request.
When the site is using ssl, however, the host header is encrypted, which introduces something of a chicken-and-egg thing: since the host header (along with all the rest of the data) is encrypted, IIS can't use that to determine which site to send the request to. And, since a SSL certificate is site-specific, IIS can't use a certificate to decrypt the data until it knows which site the request belongs to.
Thus, it widely is reported that it isn't possible to have multiple SSL sites on a single server, all sharing the same port. Enter wildcard SSL certificates and secure server bindings.
The wildcard certificate allows IIS to use the same certificate for all of the sites on a particular port. That takes away the requirement that IIS know which site's certificate to use in decrypting the data.
Secure server bindings help IIS in securing the wildcard certificate, a requirement for setting this up.
Note that a similar solution is available to apache. You can read about it here.
Thus, it is very possible to have multiple SSL sites on a single server, all sharing the same port.

Configure your sites to use host headers
Host headers are the backbone of this. Open the properties window for each site in IIS and click the advanced button. There, you can add the appropriate host header on port 80.

Creating a self-signed wildcard SSL certificate
The next step in this process is to make sure that all of the relevant sites have the same SSL port assigned to them.
In IIS, go to each site's properties and enter the appropriate port number (the same one for each site) in the SSL Port field. 443 is the default SSL port.
Note that once you do this, all but one of the affected sites will stop. That is because, at this point, IIS isn't configured to allow multiple sites to share the same SSL port.
That's OK; we'll take care of that in a moment.

Now, to generate the certificate: using SelfSSL, generate the certificate for one existing IIS site.
SelfSSL is a tool that's a part of the Microsoft IIS Resource kit.
If you haven't downloaded and installed it, you'll need to do that first.

Here's the syntax:

selfssl /n:cn=*.server.edu /s:1 /P:443 /v:3650

Where *.server.edu would be your own URL. So if you had these sites:

mysite.sharepoint.com

portal.sharepoint.com

auth.sharepoint.com

you'd use this code:

selfssl /n:cn=*.sharepoint.com /s:1 /P:443 /v:3650

It's the * that makes it a wildcard certificate.

The /s:1 is the site identification. There are a variety of ways to view a site's identifier, but the easiest simply is to open the IIS manager and click on the "web sites" tree on the left. The right-hand pane will show the description and identifier. The default site has an identifier of 1; the other sites have very long identifiers.

/P: is the SSL port you're using, and /V: is the number of days for which this cert is valid. Why would you want it to expire, anyway?

Now you've generated a wildcard certificate.

Assign the Certificate to your sites

The easiest way to assign a certificate to a site, having already created it, is to view the site properties in IIS Manager.

Click on Directory Security, and then on the Server Certificate button. This will start the wonderful wizard of IIS. You already have created a certificate, so when the wizard prompts you, choose "Assign an existing certificate." When you click next, you'll see a list of available certs, including the wildcard certificate you created.

Select this certificate, click next, and make sure you assign it the same port that was assigned to the first site.

That's it: once the wizard has finished its magic, you've assigned the cert to your site. Repeat this for all of the sites you want to secure on this port with SSL.

Configure Secure Server Bindings

This is the final step. It involves running a vbs script to set up secure bindings, which allows IIS to use host headers with SSL and secure the new wildcard certificate you created and installed.

The syntax is like this:

cscript adsutil.vbs set /w3svc/844934796/SecureBindings ":443:mysite.sharepoint.com"

The adsutil.vbs script, at least on my systems, is at C:\Inetpub\AdminScripts. You'll need to run the script command from that location.

The syntax breaks down like this:

844934796 is the site ID. Substitute your own site identifier there.

443, again, is the port

mysite.sharepoint.com is the host header for the site.

Make sure that you have this host header configured in the site properties in IIS, as well.

Run this script for each of your IIS sites. That should be all you need to do.

Start up the stopped sites after you've run cscript, and you should be good to go.

Microsoft has a few documents that run through this, but they don't really put the pieces together for you. Here they are: Configure SSL Host Headers (IIS 6.0) Configuring Server Bindings for SSL Host Headers (IIS 6.0) Obtaining and Installing a Wildcard Server Certificate

By the way: if you've done a lot of testing, and you've generated a bunch of certificates you're no longer using, you can delete them. Run mmc.exe and add the certificates snap-in. You'll want to choose the "local computer" version. Using that, you can manage all of your local certificates, including deleting the ones you no longer need.

LDAP Authentication in Mediawiki

2008-03-11T21:07:00.009-05:00

LDAP Authentication in Mediawiki isn't terribly hard, and it's so very worth it. This post shows how one implementation succeeded, and hopefully it'll help with yours. I have to say that I'm a little hesitant to post this entry, as many people have done so, and inevitably, the post becomes dated, full of deprecated and even downright wrong information. So one runs the risk in the end of being more of a hindrance than a help. So: caveat emptor. I decided in the end to write this because it took a very long time to implement this very useful extension to MediaWiki, and I hope my process will help others in their own implementation. Having said that, one really should check out all of the work Ryan Lane has done and continues to do in writing and supporting this excellent extension. The main page for the extension can be found here. These instructions were written as we implemented version 1.1g, which is one release behind the (as of this writing) current version of 1.2a. Having got that behind us, I hope this is helpful to you:

LDAP Authentication

This is a big one. It can be got from the MediaWiki Extensions Site. The instructions are pretty tough to follow, but it does work.

Here are our settings in LocalSettings.php, in which the user is authenticated against the domain, and all users except those in a select group are disallowed from logging in.

A note about SSL encryption

SSL encryption is necessary to ensure this is a secure process. Especially given that we're talking about domain usernames and passwords. So first, make sure that the apache server is requiring https when accessing the wiki. Second, for the SSL piece of the LDAP authentication to work, the wiki server has to recognize and trust the root CA of the LDAP server (or, in our case, the LDAP virtual IP fronted by a netscaler device). I hope to have instructions on doing this piece posted soon. In any case, please do this before going further.

LocalSettings.php Contents

#LDAP authentication
require_once( "$IP/extensions/LdapAuthentication.php" );
#$wgLDAPDebug = 6;
$wgAuth = new LdapAuthenticationPlugin();
$wgLDAPDomainNames = array( "DOMAINNAME");
$wgLDAPServerNames = array( "DOMAINNAME"=>"ldapserver.FQDN" );
$wgLDAPSearchStrings = array( "domainname"=>"DOMAINNAME\\USER-NAME" );
$wgLDAPEncryptionType = array( "domainname"=>"ssl" );
$wgLDAPUseLocal = false;
$wgMinimalPasswordLength = 1;
$wgLDAPBaseDNs = array( "domainname"=>"dc=tcu,dc=edu" );
$wgLDAPSearchAttributes = array( "domainname"=>"sAMAccountName" );

#Everything above is for simple LDAP authentication; the stuff below is for group syncronization
#DNs in $wgLDAPRequiredGroups must be lowercase, as search result attribute values are...
$wgLDAPRequiredGroups = array( "domainname"=>array("Fully qualified LDAP cn structure goes here") );
$wgLDAPGroupUseFullDN = array( "domainname"=>true );
$wgLDAPLowerCaseUsername = array( "domainname"=>true, );
$wgLDAPGroupObjectclass = array( "domainname"=>"group" );
$wgLDAPGroupAttribute = array( "domainname"=>"member" );
$wgLDAPGroupSearchNestedGroups = array( "domainname"=>true );
$wgLDAPUseLDAPGroups = array( "domainname"=>"true" );
$wgLDAPGroupNameAttribute = array( "domainname"=>"cn" );

#Adding new groups to the list of possible groups. This value (the group name)
#also needs to be added to the user_groups table in the database. That happens manually.
$wgGroupPermissions['groupname']['edit']= true;

The first bit should be pretty self-explanatory: load the extension, then set the basic variables as specific to your environment. In the examples here, I have used "domainname" and "DOMAINNAME" instead of our true domain name. Case matters here, so stay sharp! Note that the $wgLDAPBaseDNs variable is important; it must be correct. In most cases, this is the final bit of your domain name. For instance, if my domain is example.com, my base DN is also example.com.

Likewise, $wgLDAPRequiredGroups is very specific, and if it's not just right, the whole thing will break. Note that ldapsearch--a piece of openssl--can be an extremely useful tool in determining the correct settings for all of this. If you're using linux, you very likely already will have this tool. If you're using Windows, dsget is an equivalent (as a part of Windows Server) that can really help you out of a jam. Many will recommend a web-base ldap browser, but, honestly, I think the command-line tools like ldapsearch and dsget are much easier to use and set up.

If you're having difficulty, uncomment the $wgLDAPDebug line, and it'll provide a lot of useful debugging information within the browser session.

User Rights Management

User rights management is done through groups. We use the domain groups for this purpose, but it's also necessary to tell MediaWiki about the groups, and how we'll use them. This is done in a single step:

Defining group rights

Edit the LocalSettings.php file and add lines as below:

## The following three lines disable anonymous access
$wgGroupPermissions['*' ]['createaccount'] = false;
$wgGroupPermissions['*' ]['read'] = false;
$wgGroupPermissions['*' ]['edit'] = false;
$wgGroupPermissions['tcudba']['edit'] = true;
$wgGroupPermissions['user' ]['move'] = false;
$wgGroupPermissions['user' ]['read'] = true;
$wgGroupPermissions['user' ]['edit'] = false;
$wgGroupPermissions['user' ]['upload'] = false;

In the above example, the default group (user) only can read articles. Those who have not authenticated (*) cannot do anything in the wiki. Those in TCUDBA can edit documents.

There are a variety of possible permissions, which are detailed here. The table below shows a few of the more relevant permissions:

Permission	Description
read	allows viewing pages not defined in $wgWhitelistRead
edit	allows editing unprotected pages.
createpage	allows the creation of new pages (requires the edit right).
move	allows renaming page titles.
upload	allows the creation of new images and files.
createaccount	allows the creation of new user accounts.
delete	allows the deletion of edits and pages.

Modifying User Groups

An easy way to modify the Mediawiki database is to use PHPmyAdmin. Of course, whether you use a GUI or the command line, the data to modify is the same.

After logging in to phpmyadmin, select the WIKIDB database on the left. Then select the user_groups table and click on the browse button. You'll see the userid and group name of all the users. Note that the userid is a number; you have to use the users table to find out which userid corresponds to which username. Use this method to get back as a sysop in the wiki if you've accidentally removed yourself from that group. If you're still a sysop, you simply can use the special:userrights page.

Troubleshooting

If you receive the error basedn is not set for this type of entry, trying to get the default basedn, that means that the $wgLDAPBaseDNs variable isn't set correctly. At the time of this writing, the correct value for that is

$wgLDAPBaseDNs = array( "tcu"=>"dc=tcu,dc=edu" );

The following commands also can be helpful in determining if your system is set up correctly:

ldapsearch -x -D @TCU -W -b "dc=tcu,dc=edu" "sAMAccountName=" -H ldaps://ldap.example.com

openssl s_client -connect ldap.example.com:636

Manually Adding a User to a Group in Mediawiki

2008-03-11T20:57:00.005-05:00

If you've locked yourself out of MediaWiki administration, say, by implementing LDAP authentication, this shows you how to add a user to MediaWiki groups by editing the security tables in the database. User rights are stored in a MySQL table called user_groups. Usually you'll find it's got the wiki prefix so to give a user with the user_id of 1 bureaucrat privileges in the system wiki, use the command:

insert into user_groups values(1,'bureaucrat');

The other 'power' group is sysop:

insert into user_groups values(1,'sysop');

To find out which user_id you want to use, execute the following:

select * from user

Note that you'll need to prefix the table names with a prefix, if you set up your wiki to use one.

Copying SQL Server 2005 logins from one instance to another

2008-03-11T13:27:00.022-05:00

Copying SQL Server 2005 logins from one instance to another can be scripted pretty easily with these stored procedures from Microsoft. This is a reasonably easy task, and it's outlined in Microsoft KB article 918992. Here's the gist, if you want to get down to brass tacks quickly. Create the stored procedures sp_hexadecimal and sp_help_revlogin:

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
 DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
   @binvalue varbinary(256),
   @hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length) BEGIN   DECLARE @tempint int   DECLARE @firstint int   DECLARE @secondint int   SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))   SELECT @firstint = FLOOR(@tempint/16)   SELECT @secondint = @tempint - (@firstint*16)   SELECT @charvalue = @charvalue +     SUBSTRING(@hexstring, @firstint+1, 1) +     SUBSTRING(@hexstring, @secondint+1, 1)   SELECT @i = @i + 1 END  SELECT @hexvalue = @charvalue GO   IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL   DROP PROCEDURE sp_help_revlogin GO CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS DECLARE @name sysname DECLARE @type varchar (1) DECLARE @hasaccess int DECLARE @denylogin int DECLARE @is_disabled int DECLARE @PWD_varbinary  varbinary (256) DECLARE @PWD_string  varchar (514) DECLARE @SID_varbinary varbinary (85) DECLARE @SID_string varchar (514) DECLARE @tmpstr  varchar (1024) DECLARE @is_policy_checked varchar (3) DECLARE @is_expiration_checked varchar (3)  DECLARE @defaultdb sysname   IF (@login_name IS NULL)   DECLARE login_curs CURSOR FOR        SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM  sys.server_principals p LEFT JOIN sys.syslogins l       ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
 DECLARE login_curs CURSOR FOR
     SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
     ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
 PRINT 'No login(s) found.'
 CLOSE login_curs
 DEALLOCATE login_curs
 RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
 IF (@@fetch_status <> -2)
 BEGIN
   PRINT ''
   SET @tmpstr = '-- Login: ' + @name
   PRINT @tmpstr
   IF (@type IN ( 'G', 'U'))
   BEGIN -- NT authenticated account/group

     SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
   END
   ELSE BEGIN -- SQL Server authentication
       -- obtain password and sid
           SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
       EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
       EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

       -- obtain password policy state
       SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
       SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

           SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

       IF ( @is_policy_checked IS NOT NULL )
       BEGIN
         SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
       END
       IF ( @is_expiration_checked IS NOT NULL )
       BEGIN
         SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
       END
   END
   IF (@denylogin = 1)
   BEGIN -- login is denied access
     SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
   END
   ELSE IF (@hasaccess = 0)
   BEGIN -- login exists but does not have access
     SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
   END
   IF (@is_disabled = 1)
   BEGIN -- login is disabled
     SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
   END
   PRINT @tmpstr
 END

 FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
  END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

Having executed this script on the primary DB instance (in the master DB), you'll have two stored procedures, one named sp_hexadecimal, and the other named sp_help_revlogin. Execute sp_help_revlogin there in the master DB, and you'll get results like those below:

exec sp_help_revlogin;

/* sp_help_revlogin script
** Generated Nov 26 2007 11:29AM on PROGGY1 */

DECLARE @pwd sysname

-- Login: adm
SET @pwd = CONVERT (varbinary(256), 0x0100232E814889F09515E0F88CE4DD9)
EXEC master..sp_addlogin 'adm', @pwd, @sid = 0x3FF76B5AC6E7E5, @encryptopt = 'skip_encryption'

-- Login: Athletics
SET @pwd = CONVERT (varbinary(256), 0x010068191842A1D43B7313EEC14579B)
EXEC master..sp_addlogin 'Athletics', @pwd, @sid = 0x6C5671BA, @encryptopt = 'skip_encryption'

-- Login: bart
SET @pwd = CONVERT (varbinary(256), 0x0100252DE7AB024B287010BD25A4AB9)
EXEC master..sp_addlogin 'bart', @pwd, @sid = 0x7BEA9B86F686F, @encryptopt = 'skip_encryption'

-- Login: brs
SET @pwd = CONVERT (varbinary(256), 0x01007037A96AEE4A1695455AD0E9126)
EXEC master..sp_addlogin 'brs', @pwd, @sid = 0x344998CB53E408, @encryptopt = 'skip_encryption'

Note that the hashed values there will be considerably longer; I've truncated them for your viewing pleasure. The output above is the script you'll run on the mirror instance to create the appropriate logins. Simply copy the whole output to your mirror instance and delete the logins from the script that you do not wish to copy. This will create the logins on your mirror instance with the same passwords (if you're using SQL authentication) as on your primary instance. You'll still have to set up the server permissions on the mirror instance as they were set up on the primary, but this makes the process a whole lot easier.

SharePoint with SQL Server Mirroring

2008-03-11T12:23:00.012-05:00

While most documentation for implementing Microsoft SharePoint suggests using SQL Server clustering for high availability and failover, database mirroring might be as good or even a better option. If, for instance, you don't have a SAN, you might really appreciate the ability to use database mirroring instead. This post runs through using DB mirroring with SharePoint 2007. There is a ton of information out there about implementing Microsoft Office SharePoint Server 2007 (MOSS), but my experience is that the bulk of them fall into two basic categories: very basic step-by-step instructions that don't go into much detail on the why of it all, and those that focus entirely on the why without talking much, if at all, about the how. So you either end up with a theoretical understanding of the underpinnings of SharePoint (assuming you are familiar with the terminology), or you are able to set a basic MOSS site without knowing if you're following best-practices, or if what you're doing will even give you what you want. It's my hope--as we go through our implementation--to find and post a middle road through all this. By way of starting out, here are a few documents that have been helpful in implementing SharePoint.

Microsoft SQL Server Mirroring with SharePoint Unfortunately, this document is almost entirely instructions on how to set up database mirroring in SQL Server. If you're a DBA, this is not useful information to you, and if you're not a DBA, you're likely going to use the SQL Server GUI to implement database mirroring, anyway (the white paper gives instructions on mirroring through T-SQL commands). Happily, though, there is a bit on configuring SharePoint to use the mirror database in the event of a failover. In short: you use the SQL Alias feature (through cliconfg.exe) on the front-end web server(s). This is the method we ended up using.

Implementing DB Mirroring with SharePoint

Here's where the rubber hits the road. To be clear: we're talking SQL Server 2005, here. In short, this is what we're doing:

Mirror the SQL Server databases
Ensure the SharePoint login accounts can access the mirror DB server
Configure the SharePoint front-end web server(s) to allow for failover

At the moment, we're not going to go into much detail on setting up the mirroring; that may be added at another time. It's a good idea to set up a witness server (instance, really); that makes testing the failover much easier, and the witness instance can be a SQL Server Express instance, so it won't cost you any more. Do make sure your logins from the primary database instance are copied to the mirror instance. This KB article from Microsoft talks about doing so. Or you can see my summary in this post.

Setting up the Server Alias

Here's the money piece. SQL Server includes a "alias" feature that allows you to create a synonym, stored in the registry, for a SQL Server. This is important, because, for some reason, MOSS 2007 developers decided not to include SQL Server mirror failover capability in the product. <Actually, this decision probably is a function of the way SharePoint handles the database; it really does treat the SQL Server instance as a whole, rather than as several databases. Given that, it does follow that--since mirroring is a database-level (rather than instance-level) high-availability technology--SharePoint mightn't work quite right with mirroring. Still, it'd have been nice for them to have worked out a way for this to function.> To create an alias, run the application cliconfg.exe. This allows you to create an alias for a SQL Server machine, stored in the registry. It should be noted here that there may be two versions of this application if you're running this on a 64-bit server. They are identical except that the 32-bit version stores the alias in the 32-bit compatibility registry key. We've had instances in which we've had to set the alias up in both the standard (64-bit) and compatible (32-bit) keys in order to get SharePoint to use the alias fully. Note that when you create a TCP alias, the port matters. If you're not pointing the alias to a named instance, the port by default is 1433. Additional instances will always have a different port. You can find the port number either in the current SQL Server startup logs or by using the SQL Server configuration utility. The registry key in which the alias is stored is at

\\HKLM\Softare\Microsoft\MSSQLServer\Client\ConnectTo\

While the 32-bit compatible key is at

\\HKLM\Software\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo\

A TCP Alias breaks down something like this: DBMSSOCN,servername,4984 Where DBMSSOCN refers to TCP, servername is the server name, and 4984 is the port number. A Named Pipes Alias, on the other hand: DBNMPNTW,\\servername\PIPE\MSSQL$spstg\sql\query Where DBNMPNTW refers to Named Pipes, servername is the server name, and $spstg is the instance name. Once you've got these registry keys set, it's a simple matter of changing the server name (and port, if necessary) in the alias after a failover. This will point SharePoint to the mirror DB server instead of to the primary. If you're comfortable doing this, changing the registry directly is quite sufficient. If not, you can edit the alias with the cliconfg.exe application. After changing the alias, it's prudent to go ahead and reset IIS. You can simply run the IISRESET command from the command line to do this. It should be pointed out again that, while SQL Server DB mirroring is on a database-level (that is, a single database within an instance can fail over to the mirror), SharePoint, for the most part, treats the SQL Server instance as a unit. While it's possible to use STSADM to rename a server for a particular database, this seems like more work than is necessary. To do this failover automatically requires another step, and it's one that we're still working on. I'll post that as we move closer to that goal.