I have had the opportunity to assist a WIndows SBS MVP migrating Windows SharePoint Services 3.0 databases from SQL Server 2005 to SQL Server 2008 in a Windows SBS 2008 environment. He was following the steps outlined in this Microsoft TechNet article to move the content databases and configure them from inside SharePoint Central Administration when he hit a wall and couldn’t move forward. When he was at the point where he is already pointing to the new content database, SharePoint started throwing an error. My first instinct was that it has something to do with permissions but I need to capture some activity before even doing anything. Apparently, SQL Profiler is not installed. I opted to change the Authentication mode from Windows to Mixed mode just so I can use a SQL Server login with the SharePoint configuration. After creating a SQL Server login for SharePoint to use, everything was back to normal. While I wasn’t comfortable with using Mixed Mode authentication for SharePoint, I dug deeper to see what Windows account was being used thru the Application Pool from within IIS. It appears to be NT AUTHORITYNETWORK SYSTEM – the default account when you install Windows SharePoint Services 3.0 in the same server as the SQL Server (this is a common setup with Windows SBS). What I did was to add the NT AUTHORITYNETWORK SYSTEM account in SQL Server using the CREATE LOGIN command (you won’t be able to do this in Management Studio as the account is not exposed) and, then, assign the db_owner role in the content database.

I went back to the SharePoint Central Administration after that to switch the configuration of the content database from using a SQL Server login to using Windows authentication. That did the trick.

Note that if you are moving your SharePoint databases from SQL Server 2005 to SQL Server 2008, whether within Windows SBS or a full blown Windows Server system, make sure you check the logins as they need to be moved as well for the SharePoint application to work. You can even use the transfer SQL Server logins script from Microsoft to do this