MOSS 2007 with SP2 and STSADM backup, restore

I came across an issue in MOSS 2007 with the site collection backup and restoration. This has come up along with SP2.
When we had gone for a backup of the site collection we went ahead with the default parameter stsadm -o backup -url http://server_name/sites/site_name -filename site_name.bak
and then we restored it using the stsadm command
stsadm -o restore -url http://server_name/sites/site_name -filename site_name.bak

Once restored we faced a funny issue in which we were not able to delete Site Collection Administrators nor we were able to edit the pages.

ANALYSIS
We did our analysis and found out that when we had done a backup withe the above STSADM command, we had missed out another parameter -nositelock . This locked the site collection when the backup was done. Once restored, the lock on the site collection still persisted. We had to go Central administration page --> Application Management --> Site collection Quotas and locks link. There we have the option to remove the read only option. Once we remove the read only permission, we were able to do all site administration tasks on the site collection.

Hope this helps..
for more details you can go through
http://technet.microsoft.com/en-us/library/cc263441.aspx
http://technet.microsoft.com/en-us/library/cc262087.aspx

W3WP.exe and IIS 6.0/7.0

Today I was troubleshooting performance issues related to a sharepoint application. I came across an error in the Sharepoint log which the user had faced. It was related to Sharepoint foundation (SP2010) and was a W3WP issue.
Below is the line which I saw in the Sharepoint log.
12/15/2009 11:20:28.42 w3wp.exe (0x0F0C) 0x0668 SharePoint Foundation General 0x80070005
For my analysis, I opened the task manager and added a column PID into the Processes tab.
This PID will act as a link between the w3wp process and the application pool it is linked to.
Now open the command prompt.
set the path as C:\Windows\System32\inetsrv
Then type the command
appcmd list wp
This shall give you the list of application pool along with the PID.
Using the PID I was able to identify which application pool was consuming too much memory and was able to troubleshoot the issue.

For IIS 6.0, you can write the below command,
cscript iisapp.vbs
by going to the path C:\WINDOWS\system32.

Hope this helps someone who is having issues related to IIS worker process.

enabling Port through the Firewall for SQL Server

Found it in the net.. was quite helpful for me..
@echo ========= SQL Server Ports ===================
@echo Enabling SQLServer default instance port 1433
netsh firewall set portopening TCP 1433 "SQLServer"

@echo Enabling Dedicated Admin Connection port 1434
netsh firewall set portopening TCP 1434 "SQL Admin Connection"

@echo Enabling conventional SQL Server Service Broker port 4022
netsh firewall set portopening TCP 4022 "SQL Service Broker"

@echo Enabling Transact-SQL Debugger/RPC port 135
netsh firewall set portopening TCP 135 "SQL Debugger/RPC"
@echo ========= Analysis Services Ports ==============
@echo Enabling SSAS Default Instance port 2383
netsh firewall set portopening TCP 2383 "Analysis Services"

@echo Enabling SQL Server Browser Service port 2382
netsh firewall set portopening TCP 2382 "SQL Browser"

@echo ========= Misc Applications ==============
@echo Enabling HTTP port 80
netsh firewall set portopening TCP 80 "HTTP"

@echo Enabling SSL port 443
netsh firewall set portopening TCP 443 "SSL"

@echo Enabling port for SQL Server Browser Service's 'Browse' Button
netsh firewall set portopening UDP 1434 "SQL Browser"

@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)
netsh firewall set multicastbroadcastresponse ENABLE

Migration of SQL Server 2000/2005 to SQL 2008

In this post, I will be discussing about the migration of SQL server 2000/2005 to SQL 2008 Engine and not about the analysis, integration and Reporting services migration.

Let me first discuss about the Upgrade Stratergy first. There are two ways of upgrading the SQL server 2000/2005 to SQL 2008.
1> In-place upgrade.
2> Side by Side upgrade.

Upgrade Pre-requisites
· When planning an upgrade to SQL Server 2008, we first have to make sure that the target servers meet the necessary hardware and software requirements for SQL Server 2008 Setup to be completed.
· .NET Framework 3.5 should be installed.
· Windows Installer 4.5 should also be installed.
· Need to back up all of the system databases and user data bases
· Power shell should be installed.
· Need to run DBCCCheckDb against all databases to see the integrity of all the databases are intact.
· Finally, Run the Upgrade Advisor. Need to go through all the errors/warnings one by one and fix the errors before going forward with upgrade. Priority will be set for the errors which needs to be mandatorily fixed before the upgrade can begin.

In-place Upgrade
There are few restrictions when we are going for an In-place upgrade.

• SQL Server 2008 Setup requires that all SQL Server 2000 and SQL Server 2005 components be upgraded together. In other words, we cannot upgrade only an instance of the SQL Server 2005 Database Engine without also upgrading the Analysis Services component.
• If our legacy instance of SQL Server 2000 or SQL Server 2005 is installed on Windows 2000 Server, we must upgrade to a new server by using a side-by-side upgrade; SQL Server 2008 is not supported on Windows 2000 Server.
• A cross-platform, in-place upgrade from a 32-bit instance of SQL Server 2000 or SQL Server 2005 (x86) to a 64-bit instance of SQL Server 2008 (x64), or vice versa, is not supported.

An in-place upgrade is the fastest and easiest upgrade method because it upgrades all system and user databases and settings for us. We do not have to update client applications to connect them to a new instance of the Database Engine. In the unlikely event that an in-place upgrade of the relational Database Engine fails, we cannot quickly roll back to SQL Server 2000 or SQL Server 2005.
If the upgrade fails, we have to take the following steps:
1> From the installation media, we need to run the repair option in an attempt to fix the instance; if this does not work, we need to go for uninstall as mentioned in step 2.
2> Uninstall the corrupted SQL Server 2008 instance that was created during the failed upgrade attempt.
3> Reinstall the earlier version of SQL Server (SQL Server 2000 or SQL Server 2005).
4> Reinstall any required SQL Server service packs to your SQL Server 2000 or SQL Server 2005 instance.
5> Restore the system and user databases from database backups.
6> Review issues that prevented a successful upgrade in the previous attempt, resolve them, and restart the upgrade process.
We need to be aware that this process of in-place upgrading needs sufficient downtime, either when the upgrade is a success or when its a failure.

Activites to be done after In-place upgrade

1> Check if the Remote query timeout (s) server setting is set to 0, if not need to change.
2> Compatibility of master and User databases need to change to 100

Side by Side Upgrade

In this upgrade, we try to move all or some data from an instance of SQL 2000/2005 to SQL 2008. There can be one server move or two sever move.

In one server move, we install a new instance of SQL 2008 on the same server where SQL 2000/2005 is available.

In two server move, we install a new instance of SQL 2008 either in default or named instance on a new server. This upgrade is very time consuming in which the migration would be done manually or either by scripting. But seeing the positive side, there is no need for the application downtime, till the SQL 2008 databases are ready to be connected to the application.

Hope this helps someone...

How to change the site master page settings programmatically using PowerShell in SP2010

As I was involved in writing automation scripts to avoid the manual steps which we were intented to do, I wrote a powershell script to change the site master page settings programmatically...

-----------------------

$url = "http://servername"
$1 = "/_catalogs/masterpage/v4.master"
$2 = "/_catalogs/masterpage/nightandday.master"
$SPSite = Get-SPSite $url
$SPWeb = $SPSite.OpenWeb()
$SPSite.AllowUnsafeUpdates = "True"
## get the current status of the Site Master Page Settings in _Layouts/ChangeSiteMasterPage.aspx ##

$a = Get-SPSite $url
$b = $a.OpenWeb()
$c = $b.get_CustomMasterUrl()
$c
$d = $b.get_MasterUrl()
$d
$e = $b.get_AlternateCssUrl()
$e
#### updating the SITE MASTER PAGE SETTINGS with the new ones ####
$Custmaster = $SPWeb.CustomMasterUrl ##Site Master Page - night
$Custmaster
$SPWeb.CustomMasterUrl = $2
$SPWeb.Update()
$SPSite.AllWebs
foreach($SubSite in $SPSite.AllWebs)
{
$SubSite.AllowUnsafeUpdates = "True"
$SubSite.CustomMasterUrl = $2
$SubSite.Update()
Write-Host "$SubSite updated"
}
$SPWeb.Dispose()
$SPWeb = $SPSite.OpenWeb()
$SysMaster = $SPWeb.MasterUrl
## System Master Page - v4
$SysMaster
$SPWeb.MasterUrl = $1
$SPWeb.Update()
foreach($SubSites in $SPSite.AllWebs)
{
$SubSites.AllowUnsafeUpdates = "True"
$SubSites.MasterUrl = $1$SubSites.Update()
Write-Host "$SubSites updated"
}
$SPWeb.Dispose()
$SPWeb = $SPSite.OpenWeb()
$SPWeb.AlternateCssUrl = "/Style Library/en-us/Core Styles/my_custom.css"
$SPWeb.Update()
foreach($WebSubSites in $SPSite.AllWebs)
{
$WebSubSites.AllowUnsafeUpdates = "True"
$WebSubSites.MasterUrl = $1
$WebSubSites.Update()
Write-Host "$WebSubSites updated"
}
$SPWeb.Dispose()

--------------------

How to add users to the Visitor group of a Sharepoint 2010 application using Powershell

There has been a need in my company to add the users to the default visitor group of the sharepoint 2010 application. It is really very time consuming when you have to do it one by one using the Sharepoint UI. I have written a small script for which you can add the users to the default visitor group without any issues.
Here, in the below Script, users.txt contain the list of users that need to be added to the Visitors group...
------------------------------
$Users = Get-Content "C:\Users\service\Documents\PowerShell\users.txt"
foreach ($User in $Users)
{
$url = "http://server1"
$SPUser = New-SPUser -UserAlias $User -Web $url
$Group = "test"
$Owner = "service\account"
$SPSite = Get-SPSite $url
$SPWeb = Get-SPWeb $url
$OpenWeb = $SPSite.OpenWeb()
$OpenWeb.Update()
## can use the below two lines to add a single user to the Visitor Group
#$OpenWeb.SiteGroups.Web.AssociatedVisitorGroup.AddUser
#($Owner,"ps@ms.com",$Group,$Group)
$OpenWeb.SiteGroups.Web.AssociatedVisitorGroup.AddUser($SPUser)
$OpenWeb.Update()
$OpenWeb.Dispose()
}
--------------------------
Hope this helps some one...

Exception calling "Update" with "0" argument(s): "The web being updated was changed by an external process."

I came across the error "The web being updated was changed by an external process." when i was debugging a sharepoint code, in which I had changed the System Master Page and Site Master Page present in the page _Layouts/ChangeSiteMasterPage.aspx of the Publishing sharepoint site.

This issue came up because, the SPWeb object was not being disposed. As a result it was becoming stale. And when we try to call the update method of the SPweb object it fails.

The best way to handle this issue is to Dispose the SPWeb Object whenever possible and re-create it once again before you go ahead with any other sharpeoint change through code.

Hope this helps some one....

"The security validation for this page is invalid. Click Back in your Web browser, refresh the page, and try your operation again" error in SharePoint

When I tried to edit the home page of my application, I came across the error "The security validation for this page is invalid. Click Back in your Web browser, refresh the page, and try your operation again". This issue is caused because of security validation.

You can follow the steps to get rid of the error -
1> Go to Central Administration --> Application Management
2> Click on Web Application General Settings
3> Go to Web page Security Validation and select the Off radio button. Then click on OK.
4> Refresh the page and Iwas able to edit the page.

Hope this helps someone.

How to Disable My SIte and My Links from a Sharepoint site

There are always a need to disable my Site and My Links in MOSS 2007. Below are the steps inorder to disable the links -
1> Go to the Sharepoint Central admin page.
2> Then go to the SSP admin page.
3> Under the User Profiles and My Sites, click on the link "My Site settings".
4> In the left pane, there is a link "Personalization Services Permission". Click on that.
5> Select the user "NT AUTHORITY\authenticated users" and click on modify permissions of selected users.
6> Uncheck "Create Personal Site" and "Use Personal Features" and select "Manage Permissions" or any other permissions.

This shall fix the issue of removing the My Site and My Links from a Sharepoint Site.

An exception of type system.security.principal.identitynotmappedexception was thrown while configurating MOSS

I came across an issue in which MOSS was present in the server and i was doing a re-configuration of Sharepoint farm connecting it to a new server farm. I came across the error "An exception of type system.security.principal.identitynotmappedexception was thrown". I had checked that I had cleaned up the config database and also the admin content Database of previously set farm. I had manually deleted the web sites present in IIS related to sharepoint. I was not sure as to why this error came up.
Doing a bit of research, I came to know there was issue with the Central Admin App pool as it was running in another service account. I had deleted the Central admin website but had missed out to delete the App Pools related to sharepoint which created this issue.
Once I deleted the App Pools related to Sharepoint and started the configuration again. Everything worked perfectly fine.

Hope this experience helps someone.

STSADM technical reference

There is an excellent silverlight application which displays stsadm commands. Check out the below link for your reference.

http://technet.microsoft.com/en-us/office/sharepointserver/cc948709.aspx

error during SQL 2005 to SQL 2008 in-place upgrade

I came across a very wierd error when i was going for a in-place upgrade from SQL 2005 to SQL 2008. I had run the SQL Server upgrade Advisor to fix issues that needs to be taken care before the upgrade could take place.
I fixed the issues and then decided to do an upgrade. During the upgrade I came across an error - "Attributes do not match. Present attributes (Directory, Compressed, NotContentIndexed) , included attributes (0), excluded attributes (Archive, Compressed, Encrypted) "

To fix this kind of issue, it is important to note that the Microsoft SQL Server folder that is present in C:\program files of the server should neither be in compressed mode nor in archive mode.

If you come across this issue, please follow the below steps -
1> Check the folder "Microsoft SQL Server" in the path "C:\Program Files".
2> Right click on the Folder and select "Properties"
3> In the General Tab , there is an "Advanced" button.
4> Click on that button and see that the options "Folder is ready for archiving", "compress contents to save disk space" and "encrypt contents to secure data"are unchecked. If not, please uncheck it.

This shall resolve the issue with the SQL 2008 in-place upgrade.

to find the webparts present in a sharepoint page

It is really important for us to find out the list of web parts present in a sharepoint page. One of the tricks is to just modify the URL and we can can get the list of web parts presnet in that page.

Follow the steps as mentioned below.
1> Suppose we need to find out the web parts in the Home page of the Sharepoint site. We open the URL in the browser.
For example: http://server/pages/default.aspx . Here our aim is to find the web parts present in the default.aspx page of the sharepoint site.
2> Here we need to just modify the URL as
http://server/pages/default.aspx?contents=1 .
Appending ?contents=1 to the URL will provide you the list of web parts on that page.
Hope this helps someone.

Office Sharepoint Server search not appearing in services list

The issues pertains to search which was missing from the "services on server" display (via Central Admin -> Operations -> Services on Server). Also the server roles list was disabled.
As the search service isn't present, SSP will not be created as there are no indexers.
This issue comes up because of configuration issue. While installing MOSS 2007, if WFE option is selected and not Complete installation, this issue will come up. It is mandatory to select "Complete installation" rather than doing a "Web front End" installation.
We can confirm if the MOSS installation is a complete or WFE one by going to the PSC diagnostics logs present in "C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\LOGS". Search for the entry "Setting server role to WFE". This shall confirm if the installation is a WFE one.

Hope this helps.

AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009 when exporting data from excel

When we are exporting data from excel sheet to a table in a SQL 2005/2008 database. we come across an error "the AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009". This error comes up when the run64bitruntime is in True State.
The state can be changed to false by following the below steps.
1> go to soution explorer. Right click on the SSIS project and then click on properties.
2> Then in the property box, select debugging in the left pane.
3> Then in the right pane, we can see Run64bitRuntime is set as True. Please change it False. This shall resolve the issue.

Connect to SQL EXPRESS remotely

There has always been issues connecting to SQLEXPRESS instance using the SQL Server Management Studio (SSMS).

Steps to connect to SQL EXPRESS from other machine SSMS.

1> Enable TCP/IP
a> Launch the SQL Server Configuration Manager from the "Microsoft SQL Server 2005 CTP" Program menu
b> Click on the "Protocols for SQLEXPRESS" node,
c> Right click on "TCP/IP" in the list of Protocols and choose, "Enable"

2> Go to Surface area configuration of SQL Server. Click on Surface Area Configuration for Services and Connections.Check that Services are running. Then click on remote connection and check if Local and remote connections are selected with using both TCP\IP and named pipes option selected.

3> Providing a Port Entry
a>Go to the TCP/IP in the Protocols for SQL Express section in the configuration Manager.
b> Right Click on TCP/IP and select Properties.
c> Then select the IP Addresses tab in the Property box. Then scroll down to a entry on the right panel for "IPAll".
d> Clean up the TCP Dynamic Port option and in the TCP Port option provide a port number for example, 2301.
e> Then click OK. Restart the SQL Services.

4> A way to check the connection is using SQLCMD from a remote machine and connect like this:
SQLCMD -E -S YourServer\SQLEXPRESS,2301
The "," in the server name tells SQCMD it's a port.

5> Enabling the Firewall
a> Click on Start --> Administrative tools --> Windows Firewall with advanced Security.
b> Then right click on Inbound Rules and select New Rule. Select Rule type as Port. Click on Next. Then in Protocol and Ports, Select TCP, in the specific local Port provide the port number 2301. Then click on Next.
c> Then select the default as present and then click on Next. In the End provide the Name and click on Finish.

Once this is done, Check the connection using the SSMS from a remote machine.

STSADM find

There is an option in STSADM command line to find out the list of stsadm commands related to feature or solution or contentdb etc. Its something like, for example you want to find out what are the stsamd commands used for features...like activatefeature, installfeature etc....

Go to the path where you can run the stsadm command...i.e. in the BIN folder.

type

stsadm | find "feature"

You will get all the stsadm commands related to feature.... as shown in the screenshot below.

To determine the version of MOSS installed

There are few ways in which you can check the verison of the MOSS installed on the machine. They are -

1> Go to Central Administration Page. Click on Operations tab. Then click on "Servers in the farm" link. There the version should be available.
2> Its also visible in the site settings page of the Central Administration i.e. http://centraladminURL:portnumber/_layouts/settings.aspx.
3> Also you can run the below query on the Config database.
SELECT [VersionId],[Version],[Id],[UserName],[TimeStamp],
[FinalizeTimeStamp],[Mode],[ModeStack],[Updates],[Notes]
FROM
[SharePoint_Config].[dbo].[Versions]
WHERE VersionId = '00000000-0000-0000-0000-000000000000'
ORDER BY [Id]
DESC

Hosting Sharepoint Central Admin page on all the WFEs

Hosting a Central Admin (CA) page on all theWFEs, gives you advantage of accessing it even though if one of the WFE is down or switched off.

Steps to be taken for hosting CA on all WFEs.

1> While configuring MOSS, the configuration wizard asks whether to host the CA page on the WFE. Please go ahead and provide the port number. It will mention in the end, the URL of the CA page. Let's suppose the URL of the CA page when there is one WFE server (\\wfeserver1 ) and database server (\\dbserver ) on a port 8888 be http://wfeserver1:8888 . Verify if this URL comes up properly.
2> Go to the other WFE (\\wfeserver2 ). Again, while configuring the wizard, select "connect to existing farm" and finally would ask if the CA will be hosted on this machine. Select to proceed creating the CA page on the WFE. Finally the URL of the CA page would be http://wfeserver2:8888 . Please note that we select the same port on both the cases. Also check if the CA URL http://wfeserver2:8888 is accessible or not.

*** If we do select "This machine will not host the CA page" , then whenever we type http://wfeserver2:8888 it will automatically redirect to http://wfeserver1:8888 . So it depends on how you want to access your CA page.
3> Now once the CA page is up, Go to Operations Tab. In Global Configuraton section Click on the Alternate Access Mappings. Now Click on alternate access mapping collection and select the Central Administration. Then click on Edit Public URLs. Lets suppose you want to access the CA page with a common URL like http://wfeserver:8888 and not with http://wfeserver1:8888 or http://wfeserver2:8888 . Then in default, add http://wfeserver:8888 , in intranet you can add http://wfeserver1:8888 and in custom you provide http://wfeserver2:8888 .

This also does the load balancing for the CA page. Also if any of the WFE goes down, then you can directly access the CA URL of the server which is not down.

Learn Powershell !! The Next Big Thing.......

Powershell is basically a scripting language. It is an Object Oriented Programming language and interactive command line shell for Microsoft Windows. This scripting language can be used for various tasks like -
1> System administration
2> Automation of tasks.
3> allows you to work on Active Directory, Exchange Server, Windows Servers, SQL servers, Sharepoint Servers and other Microsoft Office Suites.
It involves and integrates with .NET environment and can also embed into other applications with ease.
Below are the few links which you should try if you want to learn powershell.
http://www.microsoft.com/windowsserver2003/technologies/management/powershell/default.mspx
http://powershell.com/cs/blogs/ebook/
http://powergui.org/

PowerGUI.org gives you a UI based powershell scripting environment with Intellisense that makes scripting more enjoiable rather than seeing a broing blue screen command prompt.

I started using powershell recently and have adapted to it very quickly provided my very less exposure to hard core coding.
I had installed the Powershell Script Editor from PowerGUI.org and wnet through the ebook present in http://powershell.com/ .

With these two in your hand, you are ready to rock and roll POWERSHELL.. Happy Learning... :)

Access is denied error when crawling a MOSS content

When we try to crawl a content souce of sharepoint site we sometimes come across the error "Access is denied. Verify that either the Default Content Access Account has access to this repository, or add a crawl rule to crawl this repository. If the repository being crawled is a SharePoint repository, verify that the account you are using has "Full Read" permissions on the SharePoint Web Application being crawled. (The item was deleted because it was either not found or the crawler was denied access to it.)" as shown in the screenshot below.

To get rid of this error, follow the below steps..

1.Click Start, click Run, type regedit, and then click OK.
2.In Registry Editor, locate and then click the following registry key:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
3.Right-click Lsa, point to New, and then click DWORD Value.
4.Type DisableLoopbackCheck, and then press ENTER.
5.Right-click DisableLoopbackCheck, and then click Modify.
6.In the Value data box, type 1, and then click OK.
7.Quit Registry Editor, and then restart your computer.

Please do the same on all the WFEs of the sharepoint farm.

Once done, kindly start the full crawling. This runs without any issues!!!

Adding PDF icon in Sharepoint

Follow the steps as mentioned below to get the PDF icon working in a sharepoint site.
1> Stop the IIS.
2> Install the Adobe IFilter from the below links -
http://www.adobe.com/support/downloads/detail.jsp?ftpID=2611 for 32 bit
http://www.adobe.com/support/downloads/detail.jsp?ftpID=4025 for 64 bit
3> Then copy/add the icon of the pdf to "c:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\IMAGES"
4> Then go to "C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\XML" and edit the DOCICON.XML
5> Add the key that is mentioned below inside the above XML file.

6> Restart the IIS.
7> Then go to Central Administration page to add the file type. Go to the SSP, then click on File types list.
8> Click on new file type. Type in PDF, then click OK.

Finally, you get to see the pdf icons for the PDF files that you have uploaded on the sharepoint site.

Windows Powershell - Changing SQL services password for a number of SQL servers

The below code doesnt have any restrictions of sql services which are in stopped mode. It can change the password for all the sql services that are running and skip the password change for the sql services that are in stopped state.

Here, ComputerListAll.txt contains the list of all the servers that are running sql services. Using the ForEach helps us to change the password simultaneously for all the machines.

$username = "ServiceAccountName"
$password = "ServiceAccountPassword"
$computers = get-content "C:\Powershell\computerlistall.txt"
forEach ($computer in $computers)
{
$sqlservices = Get-WmiObject -Namespace root\Microsoft\SqlServer\ComputerManagement10 -computername $computer -Class SqlService `
| Where-Object {$_.StartName -eq $username}
$i = $sqlservices.Count

For ($a = 0 )
{
if ($a -lt $i -and $sqlservices[$a].state -ne 1)

{
$sqlservices[$a].stopservice()
$sqlservices[$a].setserviceaccount($username,$password)
$sqlservices[$a].startservice()
}
else
{
$a++
}

if ($a -eq $i)
{
break
}

if ($sqlservices[$a].State -ne 1)
{
$sqlservices[$a].stopservice()
$sqlservices[$a].setserviceaccount($username,$password)
$sqlservices[$a].startservice()
$a++
}
else
{
$a++
}
if ($a -eq $i)
{
break
}
}
}


Write-Host "SQL passwords changed completely"

What are content types in Sharepoint?

Concept of content types
Content types are objects we create and activate typically at the level of the site collection containing the definition of one specific type of content that we want to make it available to the sharepoint users via the lists and document library containers. Content types are pacakged definitions of what we intend to store in sharepoint sites. A content type definition can include plenty of things such as columns, workflows, information management policies, document templates and more.
The creation of content type can be done -
1> in the browser.
2> delivered via a feature.
3> programmatically via object model.
Once the content type is defined, we can go to any lists and document libraries and configure the container to allow the user to create new item or document based on this content type.

Windows Powershell - Changing SQL services password if all sql services are running

Here We will be using powershell to change the password of sql services present on your machine. The below code is for the machine that hosts both sql 2008 and sql 2005. it is important to know the namespace and the class being used for the code to work properly. Note that, here all the sql services including sql agents are running on the service account. If any sql services are stopped and the logonas is still in the service account name, make sure that you change it to local system and the service is stopped.


$username = "Service Account"
$password = "Password"

In the above section we will set the username and the password as an object.

$sqlservices = Get-WmiObject -Namespace root\Microsoft\SqlServer\ComputerManagement10 -Class SqlService `
Where-Object {$_.StartName -eq $username}

For $sqlservices, we are getting the list of sql services objects which are running under the username.

$i = $sqlservices.Count

$i gives you the count of the sql services running on your machine. You can verify the same by going to services.msc using the RUN on the machine. Once that is confirmed, proceed further.

For ($a = 0 )
{
$sqlservices[$a].stopservice()
$sqlservices[$a].setserviceaccount($username,$password)
$sqlservices[$a].startservice()
$a++

As the sql services object are stored as a array in $sqlservices, we need to call each object present inside the array, then stop the service, reset the password and then start the service. Whatever is mentioned above goes in a loop, reseting the password one by one.

if ($a -eq $i)
{
break
}
}

Once the object present in the array finishes i.e. it maps with the number of objects present and then it comes out of the loop.

Write-Host "SQL passwords changed completely"

The above line just notifies that the sql password has been changed.

To run the script as a whole, copy the code in italics onto powershell command and execute it.

Error in Eventviewer - Message: Error during encryption or decryption. System error code 0.

The error "Message: Error during encryption or decryption. System error code 0." comes up in sharepoint if the updation of password hasn't taken place properly. In a farm when you have two or more front end servers we become highly dependent on the STSADM command to update the service account credentials. There are two ways to change the password for these kind of scenarios.
1> Using the UI.
2> Using the STSADM commands.

Using the UI

The places where you need to go for a service account password change is as follows -
a> Go for the password change on Sharepoint machines first.
b> In the sharepoint, go to the services using the services.msc in the RUN and then change the password of the services related to Sharepoint.
c> Open the IIS. check for the app pools that are running on the service account. Change the password there. Recycle the App Pools.
c> Open the central admin page then go to operations tab. Then click on services on server link. Then click on the services that are running and change the password there.
d> Click on the Shared Services link on the left side pane. that will open up the SSP which you have configured. Select the SSP and click on Edit properties. There also the password must be updated.
e> Finally, open the SSP and change the password in the default access account.

When we are following the password change using the STSADM commands, try to follow the steps as mentioned below.

Using the STSADM commands

Central Admin

1. stsadm -o updatefarmcredentials -userlogin [Service Account] -password [Password]
IISRESET /NOFORCE

NOTE:
One or more errors deploying administration application pool credentials. Please check the application event log and fix manually. This command must either be used on a server where Central Administration is running, or you must specify the -local parameter.

There was an error encrypting or decrypting credentials. Either a credential update is currently being performed, or you must update the farm account credentials on this server before you can perform this task. Run the above command with -local parameter

App pool password change

2. stsadm -o updateaccountpassword -userlogin [Service Account] -password [Password] -noadmin

IISRESET /NOFORCE

NOTE: in case there is an error in the above step saying "please retry manually”. Re-run the above script after opening the Central Admin site.

WSS Search Help

3. stsadm.exe -o spsearch -farmserviceaccount [Service Account] -farmservicepassword [Password]

4. stsadm.exe -o spsearch -farmcontentaccessaccount [Service Account] -farmcontentaccesspassword [Password]

SSP

5. stsadm.exe -o editssp -title [Name of SharedServices] -ssplogin [Service Account] -ssppassword [Password]

Office SharePoint Service Search

6. stsadm.exe -o osearch -farmserviceaccount [Service Account] -farmservicepassword [Password]

7. Finally, Manually change the password for “Default content access account”

Your current security settings donot allow this file to be downloaded.

This error comes up when the Internet Explorer is at its best in securing your machine and network. Inorder to download anything from the internet which is essential, you need to change the settings in the internet explorer.

1> Open the Internet Explorer
2> Click on Tools --> Internet Options
3> select the security tab.
4> The zone selected should be internet as you want to download a file from the net.
5> Then click on "Custom Level" button.
6> Another box comes up. Then scroll down and check for "downloads" setting.
7> Enable File download.
8> Enable Automatic Prompting for File downloads.
9> Close the IE and open again.

This should help you in downloading the file from the net.

Got irritated by the Sharepoint GUID naming conventions!!!!!!!

Give your own naming conventions to the sharepoint databases instead of the GUID names generated on its own.

There is always a creation of the sharepoint admin content database when we configure MOSS 2007. If we are using a single database server and have multiple MOSS farms, then problems comes up related to manageability of the databases. There is always confusion among the sharepoint content DBs as they are differenciated from each other by the GUIDs mentioned in their names. To avoid such confusions, follow the below steps-
For example,
1> Take a backup of the admin content database "SharePoint_AdminContent_046d584c-2f4b-49a3-93df-a7bd0dde771f" and restore it as "Servername_Sharepoint_AdminContent" on the same database server.

2> create a command file with the below details and name it as renameDB.cmd. Please note the the text between [] should be provided by you before you run the command file.
Rem splpri = SQL server
Rem mossdb_old = old Moss content Database
Rem mossdb_new = new Moss content Database
Rem mosssite = Moss URLRem
sspadm = SSP login acct
Rem ssppass = Password for ssp login
Rem domain = domain for sspadmin login

SET sqlpri= [Database Server Name]
SET mossdb_old=SharePoint_AdminContent_046d584c-2f4b-49a3-93df-a7bd0dde771f
SET mossdb_new=ServerName_Sharepoint_AdminContent
SET mosssite=http:// [Central Administration URL]
SET sspadm= [Service Account]
SET ssppass= [Service Account pwd]
SET domain= [Service account Domain]
--------------------------ECHO.
ECHO Preparing %mosssite% Content Databasestsadm -o preparetomove -contentdb %sqlpri%:%mossdb_old% -site %mosssite%

ECHO Removing content database for %mosssite%
stsadm -o deletecontentdb -url %mosssite% -databasename %mossdb_old% -databaseserver %sqlpri%

ECHO Stopping and restarting IISECHO.

iisreset /noforce

ECHO.

ECHO Adding content database for %mosssite%

stsadm -o addcontentdb -url %mosssite% -databasename %mossdb_new% -databaseserver %sqlpri%

ECHO Stopping and Restarting IIS

ECHO.

cd\iisreset

Create a CMD File

1> Create a new text document.
2> Copy paste the highlighted text as mentioned above into the text file
3> Rename the text file as RENAMEDB.cmd
4> This shall create the command file.

Once the command file is created, Copy the command file to the BIN folder of the sharepoint i.e. "C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN" in the web server. Then from the command prompt go to the above path and type RENAMEDB.cmd and press enter. This should be it.

You can verify the same by going to application management--> content databases--> then select the central administration. Here you can see your new content database name coming up.

You can go back to the database server and delete the "SharePoint_AdminContent_046d584c-2f4b-49a3-93df-a7bd0dde771f".

Integrating SQL 2008 SSRS with MOSS 2007

Pre-requisites -

a> MOSS 2007 installed on the machine.
b> SQL 2008 installed with the report server configuration to be done.

Today I will be discussing the configuration of Reports 2008(as i selected to configure it later) which can be integrated with MOSS 2007. The steps are as follows -

1> Go to Start --> All Programs --> Microsoft SQL Server 2008 --> Configuration Tools --> Reporting services Configuration Manager. The page opens up as shown in the image below. See that the report Service status is started.

2> Fill up the details about the service account that will be used to the report server database and also the sharepoint config and the content database as shown in the screenshot below.

3> Provide the details of the report server web service URL. you can keep it as default or you can provide details as shown in the screenshot below.

4> Coming to the database, we need to click on Change database button inorder to configure the report server databases.

5> After Clicking on the Change Database button, the Change Database page comes up. Here we can either connect to an existing Reporting Server Database or create a new Report Server database. Then click on Next after selecting the Create new Report Server Database as shown below.

6> Then provide the Database server name with the authentication type as Integrated Security. Then click on Next.

7> we then come to the database page where we mention the name of the report server database and also where the report server should be configured in Native mode or in Sharepoint Integrated mode. As we are integrating SSRS 2008 along with MOSS 2007, we select the latter option. Then we click on Next.

8> Finally the configuration process takes place and once done, we click on finish.


9> Once the configuration is done, we come back to the main page of Reports Services Configuration Manager where all the details are reflected as per the setting we did in the above steps.

10>Next we do the configuration of the Report manager URL as shown in the screenshot below. We can keep the defaults also.


11> Then we do the email settings as shown in the screenshot below.


12> we can keep the execution account, encryption keys and scale out deployment as it is without any changes unless and until our requirement makes these mandatory for implementation.


Once this is done, we are ready for the sharepoint integration from SQL 2008 side.


In the next Blog, I will be explaining about the configuration from Sharepoint (MOSS 2007) side.

Moving the tempDB files once the disk is full

Problem Description

If we come across following errors in log file Source:
MSSQLSERVER Event ID: 17052
Description: The LOG FILE FOR DATABASE 'tempdb' IS FULL. Back up the TRANSACTION LOG FOR the DATABASE TO free up SOME LOG SPACE.

Solution Description

we need to make sure that TempDB is set to autogrow and we do not set a maximum size for TempDB. If the current drive is too full to allow autogrow events, then we can arrange a bigger drive, or add files to TempDB on another device (using ALTER DATABASE as described below and allow those files to autogrow. )

Move TempDB from one drive to another drive. There are major two reasons why TempDB needs to move from one drive to other drive.
1) TempDB grows big and the existing drive does not have enough space.
2) Moving TempDB to another file group which is on different physical drive helps to improve database disk read, as they can be read simultaneously.
We need to follow direction below exactly to move database and log from one drive (c:) to another drive (d:) and (e:).

Open Query Analyzer or SSMS and connect to your server.

Run this script to get the names of the files used for TempDB.

USE TempDBGOEXEC sp_helpfileGO

Results will be something like:
name fileid filename filegroup size------- ------ ---------------------------------------tempdev 1 C:\Program FilesMicrosoft SQL ServerMSSQLdatatempdb.mdf PRIMARY 16000 KB
templog 2 C:Program FilesMicrosoft SQL ServerMSSQLdatatemplog.ldf NULL 1024 KB

along with other information related to the database.
The names of the files are usually tempdev and templog by default.
These names will be used in next statement. Run following code, to move mdf and ldf files.

USE master
GO
ALTER DATABASE TempDB MODIFY FILE (NAME = tempdev, FILENAME = 'd:datatempdb.mdf')
GO
ALTER DATABASE TempDB MODIFY FILE (NAME = templog, FILENAME = 'e:datatemplog.ldf')
GO
The definition of the TempDB is changed. However, no changes are made to TempDB till SQL Server restarts. Please stop and restart SQL Server and it will create TempDB files in new locations.

Windows Server 2003 Event ID 8 and source crypt32

Problem Description

There are errors coming up in the Event viewer "Failed auto update retrieval of third-party root list sequence number from: with error: This network connection does not exist." This causes the eventviewer filled up unnecessarily

Solution Description

This can be troubleshooted as follows -
1> Go to "Add/Remove programs" in the server.
2> Click on "Add/remove Windows Components".
3>uncheck the "Update Root Certificates".

This will resolve the crypt32 logging issue in the event viewer

SQL Server databases are not supported on compressed volumes

Problem Description

This is related to the MS SQL server administration. There is a common mistake of compressing the drives instead of shrinking the files to gain more space in the server. It is important to understand that SQL Server database on compressed volumes are not supported. There are two scenarios one for SQL 2000 and other one is for SQL 2005.

SQL 2000
In SQL Server 2000, you could create a new database with its .mdf and .ldf files located on NTFS or FAT compressed drives. The interface doesn't restrict you from doing this

SQL 2005
As opposed to SQL 2000, SQL 2005 doesn't even allow you to place data and log files of Read/Write databases on compressed volumes.
For example, Let's create a database called Compressed_DB with .mdf and .ldf files located on a compressed file system (E drive in my case here). You will get an error: Create failed for Database 'Compressed_DB'. (Microsoft.SqlServer.Smo) An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) The file "E:\Compressed_DB.mdf" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed. CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 5118) This is because a read/write databases primary .mdf and .ldf files cannot be created on a compressed volumes in SQL 2005. Also, if you try to restore a database backup (.bak) file of a read/write database with its .mdf or .ldf or both pointed on a compressed drive, you will again get an error as follows: Restore failed for Server ''. (Microsoft.SqlServer.Smo) System.Data.SqlClient.SqlError: The file "e:\Db.mdf" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed. (Microsoft.SqlServer.Smo)

Solution Description

Log files should never be placed on compressed file systems. System database (master, msdb, model and tempdm) cannot be placed on compressed drives. If you want to restore or take a backup please be sure that your drive is not compressed.

Incorrect syntax near COLLATE. Error When Trying to Join a SharePoint Farm

Problem Description

I encountered another nasty bug while rebuilding our Microsoft Office SharePoint Server (MOSS) 2007 Development environment (DEV). Since the time I originally created DEV, I installed SQL Server SP2 and also restored several legacy databases to support the development of the next version ("v2") of our solution. After running the SharePoint Products and Technologies Configuration Wizard on the SSP server to create the farm, I ran the wizard on the first front-end Web server in order to join it to the new farm. On the second step of the configuration wizard, I left the default option selected (Yes, I want to connect to an existing server farm) and then clicked Next. On the Specify Configuration Database Settings step, I typed the name of the SQL Server and then clicked Retrieve Database Names. At this point, I was presented with the "Unhandled exception" dialog displayed by the .NET Framework when something very bad happens. Error message: Unhandled exception has occurred in your application. If you click Continue, the application will ignore this error and attempt to continue. If you click Quit, the application will close immediately. Incorrect syntax near 'COLLATE'. You may need to set the compatibility level of the current database to a higher value to enable this feature.

See help for the stored procedure sp_dbcmptlevel. Details: See the end of this message for details on invoking just-in-time (JIT) debugging instead of this dialog box. ************** Exception Text ************** System.Data.SqlClient.SqlException: Incorrect syntax near 'COLLATE'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.SharePoint.PostSetupConfiguration.SqlSession.ExecuteNonQuery(SqlCommand command) at Microsoft.SharePoint.PostSetupConfiguration.SqlServerHelper.DatabaseTableWithColumnExists(String table, String column) at Microsoft.SharePoint.PostSetupConfiguration.SqlServerHelper.GetV3WSSConfigurationDatabases() at Microsoft.SharePoint.PostSetupConfiguration.ConnectConfigurationDbForm.GetDatabasesButtonClickEventHandler(Object sender, EventArgs e) at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

Solution Description

whenever we try to join the server farm, the web server scans for the databases in the SQL server. If it finds any legacy database in that instance of SQL server, the connection breaks throwing this error. It is important the the compatibility level of these databases are raised to the recent most before we try to join the sharepoint to a farm.

Check for the compatibility using the queries mentioned below. And once we find the database we upgrade it to the latest compatibility level.

sp_dbcmptlevel [ [ @dbname = ] dbname ] [ , [ @new_cmptlevel = ] version ]

example- suppose the db DBTEST is 6.0 compatible then we are changing its compatibility by using the query

sp_dbcmptlevel [ [ @dbname = ] DBTEST ] [ , [ @new_cmptlevel = ] 80 ]
version

80 = SQL Server 2000
90 = SQL Server 2005
100 = SQL Server 2008

SSIS and SQL Server Named Instance issue

Problem Description

There are deployment issues when we try to deploy SSIS package on to a named instance SQL server with no default instance of it and store it in the SQL server. The errors that basically come out are the timeout errors. Even the event viewer would shows the same error.

Solution Description

SSIS runs as a service, and only one instance of SSIS can run on on platform. If we are working with one Named instances of SQL Sever the SSIS service will by default use the default instance to store packages (if we chose to store in the SQL sever). If we don't have a default instance or if we want to use a different SQL server to store your packages, we must alter the SSIS configuration file. we can find the file in: the SQL Sever install directory \90\DTS\Binn (e.g. c:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml) we change the ServerName attribute and this should work. Check the image for the same.

Let me know if this post helps anyone.