Don't upload Microsoft Access Databases into SharePoint

My favorite word is No. But I like to follow No with But.

Often, site owners ask me to allow upload of Microsoft Access files by disabling the block of the .mdb file extension. Here's my template "No...but" response.

We will continue to block MDB files due to the fact that unsafe code can be included within an Access database. However, Office Access 2007 allows code to be either verified as safe or disabled so the ACCDB extension can be uploaded into SharePoint.

Here are steps to convert the database from MDB to the safe ACCDB format: http://office.microsoft.com/en-us/access/HA100908451033.aspx

Alternatively, If you can’t convert to Access 2007, consider publishing any data in Access 2003 dbs (like flat tables) into SharePoint lists.

We've deployed the Office 2007 clients globally so it's easier for me to say no in this case. My goal is to get people to adopt SharePoint...and with deeper integration and more Access-like features coming in SharePoint 2010, they're going to be better off moving away from a strictly Microsoft Access app.

Move SharePoint Databases without a DBA

Did you ever get a request to move 198 SharePoint databases? Every time I think about working with SQL Server, I remember a session at The March 2008 SharePoint Conference titled, "SharePoint Admin -- the Reluctant DBA." I live the life of the reluctant DBA every day, so I didn't attend that session, but I remember the title.

I inherited a SharePoint development farm. I didn't build it and up until 7 weeks ago, I didn't even know the server names. Now I'm the contact for making decisions about these servers.

Apparently, our SAN guys gave the database server running SQL a new 650GB LUN to present as a disk where content databases will reside and now they want the old 250GB LUN back. So I get an e-mail from one of the SAN guys in India asking to move the content from the old disk to the new disk. Fair enough. I go to see what is on the disk a see 198 SQL MDF data files and 198 SQL Log files (design side note: don’t put data and log files on the same disk – this is a DEV farm where it’s not as critical to follow this rule – definitely follow the rule in your Stage and Prod environments).

Did I mention I work for a large, global corporation? Well we got DBAs. However, to get the DBAs to work for you, you need to submit a Request For Service (It's a Microsoft Word Form) providing an overview of what needs to be done to a Group Mailbox. The people who maintain the mailbox then assign it to the responsible group (I cross my fingers at that point because I once submitted one of these requests to create a new Service Account and they assigned it to my boss who called me and asked what I needed done -- it eventually got assigned to the AD Design guy who sits 2 aisles away from me). After the request is assigned, you wait in queue. This is where I become the reluctant DBA -- I hate to wait in lines.

Okay, so how do you move 198 databases? Find an account that has Sysadmin rights in SQL -- I got lucky and the guy that built the farm used one of my accounts to install. Kick your developers out -- be nice. Verify the database backup ran.

This will take approximately one hour -- Somebody time me:

1. Log on to the server running SQL Server 2005 (1 minute).

2. Open up SQL Server Management Studio and connect to your SQL Instance where the Databases reside (1 minute).

3. Run the following query to generate T-SQL to set all your DBs OFFLINE (Again got lucky -- I ran a google search for "sql server detach multiple databases" and got this post first hit. Thanks vidhya sagar at SQL Servercentral.com -- This is great approach. With SQL Server 2005's "ALTER DATABASE...MODIFY FILE" capability, I modified to just set databases Offline and then bring back Online after files are copied to new location. (1 minute):

set nocount on
declare @dbname as varchar(80)
declare @server_name as varchar(20)
select @server_name = @@servername
declare rs_cursor CURSOR for select name from master.dbo.sysdatabases where name not in ('model','master','msdb','tempdb','alert_db','mssecurity') and filename like 'E:\MSSQL2K\Data%'
open rs_cursor
Fetch next from rs_cursor into @dbname
IF @@FETCH_STATUS <> 0
PRINT 'No database to backup...Please check your script!!!'
WHILE @@FETCH_STATUS = 0
BEGIN
print 'ALTER DATABASE ' + @dbname + ' SET OFFLINE WITH ROLLBACK IMMEDIATE'
print 'go'
print 'print ''Setting of ' + upper(@dbname) + ' database to OFFLINE successfully completed'''
print 'go'
PRINT ' '
FETCH NEXT FROM rs_cursor INTO @dbname
END
CLOSE rs_cursor
deallocate rs_cursor
print ' '
print 'print ''SERVER NAME : ' + upper(@server_name) + '--> All databases successfully set OFFLINE'''

4. Run the following query to generate T-SQL to set the new file location and bring your DBs back Online(modify for your environment) (1 minute):

set nocount on

declare @dbname as varchar(80)

declare @server_name as varchar(20)

select @server_name = @@servername

declare rs_cursor CURSOR for select name from master.dbo.sysdatabases where name not in ('model','master','msdb','tempdb','alert_db','mssecurity') and filename like 'E:\MSSQL2K5\Data%'

open rs_cursor

Fetch next from rs_cursor into @dbname

IF @@FETCH_STATUS <> 0

PRINT 'No database to backup...Please check your script!!!'

WHILE @@FETCH_STATUS = 0

BEGIN

print 'USE [master]'

print 'GO'

print 'ALTER DATABASE ' + @dbname

print 'MODIFY FILE'

print '(NAME = '''+ @dbname + ''', FILENAME = ' + '''G:\MSSQL2K5\Data\' + @dbname + '.mdf''' + ')'

print 'GO'

print 'ALTER DATABASE ' + @dbname

print 'MODIFY FILE'

print '(NAME = '''+ @dbname + '_log'''+ ', FILENAME = ' + '''H:\MSSQL2K5\Log\' + @dbname + '_log.ldf''' + ')'

print 'GO'

print 'ALTER DATABASE ' + @dbname + ' SET ONLINE'

print 'GO'

print 'print ''Setting of ' + upper(@dbname) + ' database to ONLINE successfully completed'''

print 'go'

PRINT ' '

FETCH NEXT FROM rs_cursor INTO @dbname

END

CLOSE rs_cursor

deallocate rs_cursor

print ' '

print 'print ''SERVER NAME : ' + upper(@server_name) + '--> All databases successfully set ONLINE'''

5. Copy the output from Step 3 and execute it. All the content db's will be set Offline!!!! (5 minutes)


6. Copy your files to the new location -- WARNING: don't do a cut and paste. COPY FILES -- I've witnessed attaches through SQL GUI fail with the result of data files magically disappearing (Don't get me started about what it takes to get our off-shore Backup guys to restore files from tape) (10 minutes).


7. Copy the output from Step 4 and execute it. All the content db's will be brought back on-line pointing to new file locations!!!! (5 minutes)


8. Verify databases are accessible. (31 minutes)


9. Delete the Old Files. (5 minutes)

Update SharePoint web.config with Powershell

Had a need to update 48 web.config files in a SharePoint farm. Purpose was to add Novell E-Directory as an authentication provider for SharePoint Farm in our DMZ consisting of 4 web applications (8 IIS websites by virtue of extending) load balanced across 6 Web Front Ends (more on that in a future post).

To add the provider, there are several web.config additions...and I'm sick of writing complex checklists for our operations folks to carry out the changes. I was also a little worried about them missing some of the additions. I prefer to have them perform one step -- double-click "webconfigupdate.bat" -- and be done rather than manually having them open, edit, and save 48 web.config files.

Powershell and the SPWebConfigModification Class to the rescue.

• Download and install Powershell 1.0 here (you only have to install on one server in your farm where SharePoint is installed -- wherever you have Central Administration site running is fine).
  
• MSDN info about SPWebConfigModification is here.
  

Credit needs to go to following -- I was able to merge the ideas from the code in the first two posts to get my final powershell script:

• This is post by Raymond Mitchell that got me started. It's great sample Powershell script for single web app. Although, I needed to loop through every web app in my farm.
  
• This is the post by Gary Lapointe where I found how to use Powershell to loop through web apps on a farm – it's not the first time Gary's blog helped me understand some administration scripting.
  
• Mark Wagner has some nice guidance on this topic. Helped me understand errors I received.
  
• Reza Alirezaei has a great post on SPWebConfigModification's Top 6 Issues which helped me confirm much of the oddness that resulted when testing.
  
• Gotta thank "The Groom," a team member who got the SPWebConfigModification class working in a Solution he deployed. I tried the SPWebConfigModification class once before but abandoned it because of issues I was seeing based on Reza's post. The Groom's success inspired to give it another try.
  

Before starting, SPWebConfigModification Class requires XPath values for its "Path" and "Name" Properties. The Path and Name values are how the Class finds the location in the web.config to add, update, and remove values.

The BIT-101 XPath Query Tool is a nice, free, on-line tool for finding correct XPath to enter. I used the tool by simply copying my web.config – with new elements and attributes I wanted in my final web.config -- and pasting it into this java based tool. I than queried for the new elements and attributes to determine the XPath I would use in my script.

There are some nice examples here to help you understand XPath if you're not familiar.

Here's the code I threw into a Powershell .ps1 file I named webconfigchange.ps1:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
$farm = [Microsoft.SharePoint.Administration.SPFarm]::Local
$websvcs = $farm.Services  where -FilterScript {$_.GetType() -eq [Microsoft.SharePoint.Administration.SPWebService]}
#use powershell method for suppressing errors for reason on next line -- updates will run successfully even though errors are thrown -- comment out erroractionpreference variable when debugging
#If more than one call to the ApplyWebConfigModifications() method is made in close succession you will get the following error due to timer job:
#A web configuration modification operation is already running.
$erroractionpreference = "SilentlyContinue"
 
#loop through each portal web app
foreach ($websvc in $websvcs)
{
foreach ($webapp in $websvc.WebApplications)
    {
    #update web.config for all web apps in farm excluding mysites and ssp
    if ($webapp.name -like "*my*") {write-output "skipping mysites..."}
    elseif ($webapp.name -like "*ssp*") {write-output "skipping SSP..."}
    else
        {
        #add peoplepicker node for IDM – IDM is name we will use for Authentication Provider for Novell Identity Management
        #here's where you'll need to understand XPath – this variable captures the xpath query – later in script, I'll optionally use Name and Path instead of Argumentlist
        $ppMod = New-Object -TypeName "Microsoft.SharePoint.Administration.SPWebConfigModification" -ArgumentList "add[@key='IDM']", "configuration/SharePoint/PeoplePickerWildcards"
        $ppMod.Sequence = 0
        $ppMod.Owner = "SimpleSampleUniqueOwnerValue"
        $ppMod.Type = "EnsureChildNode"
        #this is actual value which will be added or modified
        $ppMod.Value = "<add key='IDM' value='*' />"
        $webapp.WebConfigModifications.Add($ppMod)
 
        #add membership node
        $memberMod = New-Object -TypeName "Microsoft.SharePoint.Administration.SPWebConfigModification" -ArgumentList "membership", "configuration/system.web"
        $memberMod.Sequence = 0
        $memberMod.Owner = "SimpleSampleUniqueOwnerValue"
        $memberMod.Type = "EnsureChildNode"
        $memberMod.Value = "<membership defaultProvider='IDM'></membership>"
        $webapp.WebConfigModifications.Add($memberMod)
  
        #add providers node
        $providerMod = New-Object -TypeName "Microsoft.SharePoint.Administration.SPWebConfigModification" -ArgumentList "providers", "configuration/system.web/membership"
        $providerMod.Sequence = 0
        $providerMod.Owner = "SimpleSampleUniqueOwnerValue"
        $providerMod.Type = "EnsureChildNode"
        $providerMod.Value = "<providers></providers>"
        $webapp.WebConfigModifications.Add($providerMod)
 
        #add IDM LDAP attributes – here, we're using Path and Name properties instead of Argumentlist
        $ldapMod = New-Object Microsoft.SharePoint.Administration.SPWebConfigModification
        $ldapMod.Path = "configuration/system.web/membership/providers"
        $ldapMod.Name = "add[@name='IDM'][@type='Microsoft.Office.Server.Security.LDAPMembershipProvider, Microsoft.Office.Server, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71E9BCE111E9429C'][@server='<your Ldap server>'][@port='389'][@useSSL='false'][@useDNAttribute='false'][@userDNAttribute='cn'][@userNameAttribute='mail'][@userContainer='<your Ldap path>'][@userObjectClass='person'][@userFilter='(ObjectClass=*)'][@scope='Subtree'][@otherRequiredUserAttributes='sn,givenname,cn']"
        $ldapMod.Sequence = 0
        $ldapMod.Owner = "SimpleSampleUniqueOwnerValue"
        $ldapMod.Type = "EnsureChildNode"
        $ldapMod.Value = "<add name='IDM' type='Microsoft.Office.Server.Security.LDAPMembershipProvider, Microsoft.Office.Server, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71E9BCE111E9429C' server='<your Ldap server>' port='389' useSSL='false' useDNAttribute='false' userDNAttribute='cn' userNameAttribute='mail' userContainer='<your Ldap path>' userObjectClass='person' userFilter='(ObjectClass=*)' scope='Subtree' otherRequiredUserAttributes='sn,givenname,cn' />"
        $webapp.WebConfigModifications.Add($ldapMod)
 
        #save changes and apply to farm
        $method = [Microsoft.Sharepoint.Administration.SPServiceCollection].GetMethod("GetValue", [Type]::EmptyTypes)
        $closedMethod = $method.MakeGenericMethod([Microsoft.Sharepoint.Administration.SPWebService])
        $services = $webapp.Farm.Services
        $service = $closedMethod.Invoke($services, [Type]::EmptyTypes)
        $service.ApplyWebConfigModifications()
        $webapp.Update()
        }
    }
}

I call the above script to run from a .bat file with below commands:

powershell.exe Set-ExecutionPolicy RemoteSigned
REM command to run script from the ps1 file
powershell.exe -noexit .\webconfigchange.ps1

So there it is. With double-click of batch file, 48 web.config files are modified. If I ever want to remove the values, I can run same ps1 but replace every "WebConfigModifications.Add" entry with "WebConfigModifications.Remove." Another thing I like about SPWebConfigModification Class is that all the changes are stored in the SharePoint configuration database so if I ever add new front ends, the web.config values are automatically populated.