Thursday, July 12, 2012

Fetch Database Name From Database Id In SharePoint Farm

Fetch Database Name From Database Id In SharePoint Farm

When working with SharePoint Databases oftentimes the only way SharePoint identifies the database is via a GUID. Sometimes this can be frustrating because you want to know exactly what database you are working with. As far as i know there is no STSADM command available to easily translate GUID to Database name.

Word of warning, the following script directly queries a SharePoint database; this is never a good idea. Therefore I recommend you restore the database to a restore or test server where you can execute the query as to prevent any potential issues with your production environment.

Luckily there is a way to get this information from SQL Server, to do this you must run a query against the Configuration database (See warning above).

First you need to open Management Studio
 1. Select New Query.
 2. Select the Configuration Database from the Available Databases dropdown list.
 3. Copy the script below and paste it into the Query window and press Execute.

Select ID, Name from objects
 where properties like
 '%Microsoft.SharePoint.Administration.SPContentDatabase%m_nWarningSiteCount%'

This will output the Database GUID and Database Name in a nice list, giving you the data you need to identify what database SharePoint is talking about when it is talking GUID’s to you.

As stated There is some risk involved in running queries against SharePoint databases, Microsoft advises not to run any query against a SharePoint database as it might interfere with any SharePoint processes running against the databases. When this happens the database will lose support status and if you need to contact Microsoft for support with issues in the future you might be asked to replace the database with a fresh copy. I usually restore a recent backup of the configuration database to a restore server or a test server and run the query there. This allows me to get the data but still comply with the “No query for you!” rule that SharePoint databases have.

Friday, August 6, 2010

Configuring Forms Based Authentication For SharePoint 2010


Hi All,


Everyone is wondering why SharePoint 2010 Forms Based Authentication is a head ache. Guess what, its no longer is...!
The Security of SharePoint sites in 2010 are increased than ever before....!!!
The 8 Simple Steps for Configuring SharePoint 2010 FBA site

1.      Creating Database (aspnetdb) :

a)     Note : “Choose any Database name. In this example we are using aspnetdb

Go to Start > Run > paste the below address in the address bar
C:\Windows\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe
Click OK to launch.


b)     Choose ‘Configure SQL Server for Application Services’ and click Next.

c)      Click Next and enter the name of the SQL Server, in this case the server name will be FBAServer.

d)     Confirm that we have our Database (aspnetdb) created in SQL Management Studio on the SQL server.

2.      Create Users in IIS 7.X
 Using IIS to Populate Data for Database (aspnetdb) We Created in Step Before.
a.       Open IIS Manger on Server
b.       Select Default Web Site and Make Sure you are in Features View

c.       Open Connection Strings Option under ASP.NET Section.

d.       Click on LocalSqlServer Connection Sting & Provide SQL Server Name where you created aspnetdb in Earlier Step also Provide name of Database Created.

Click ok
e.       Once again Select Default Web Site & from Features View Click on .NET Users

f.        Click on Add and Create New user in aspnetdb


Click Finish
g.       Create more users by repeating “steps f and g”.

3.      Login to the SharePoint server.
a.       Create a Claims Based Authentication Mode web application from Central Administration and assign a port number, application pool, and database name. The settings around Claims are below.



4.      Open up IIS (inetmgr).
a.       Select the SharePoint web application that you created and right-click. Click Explore and open the web.config in Notepad.
b.       Find the line <system.web> in the web.config. Add the code below between the </SharePoint> and <system.web>  tags.

Note : “Make sure that the Data Source name is changed.”

<connectionStrings>
    <add name="SqlAuth" connectionString="Data Source=FBAServer;Integrated Security=SSPI;Initial Catalog=aspnetdb;" />
</connectionStrings>

c.       Find the <add name=“i” tag, and then add the code below on the next line. (That is, add the code after the full <add name=“i” … /> statement.)
<add name="SQLMP" connectionStringName="SqlAuth" applicationName="/" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.50727.4927, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
d.       Find the <add name=“c” tag, and then add the code below on the next line (that is, add the code after the full <add name=“c” … /> statement).
<add name="SQLRP" connectionStringName="SqlAuth" applicationName="/" type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.50727.4927, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
5.      In IIS Manager, right-click Central Administration v4 and click Explore. Open the web.config in Notepad.
a.       Find the line <system.web> in the web.config. Add the code below between the </SharePoint>  and <system.web> tags.
<connectionStrings>
    <add name="SqlAuth" connectionString="Data Source=FBAServer;Integrated Security=SSPI;Initial Catalog=aspnetdb;" />
</connectionStrings>

b.       Find the <roleManager and <membership tags in the web.config.
<roleManager>
<providers>
</providers>
</roleManager>
<membership>
<providers>
</providers>
</membership>

c.       Replace the above with:
<roleManager enabled="true" defaultProvider="AspNetWindowsTokenRoleProvider">
      <providers>
<add name="SQLRP" connectionStringName="SqlAuth" applicationName="/" type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.50727.4927, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
</providers>
    </roleManager>
<membership>
      <providers>
<add name="SQLMP"  connectionStringName="SqlAuth" applicationName="/" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.50727.4927, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
      </providers>
</membership>

6.      In IIS Manager, expand the SharePoint Web Services entry and right-click the SecurityTokenServiceApplication. Click Explore. Open the web.config in Notepad.
a.       Scroll to the bottom of the file and find the </system.net> tag, and add:
<connectionStrings>
   <add name="SqlAuth" connectionString="Data Source=FBAServer;Integrated Security=SSPI;Initial Catalog=aspnetdb;" />
</connectionStrings>
  <system.web>
   <membership>
    <providers>
   <add name="SQLMP" connectionStringName="SqlAuth" applicationName="/" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.50727.4927, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
    </providers>
   </membership>
   <roleManager enabled="true" >
    <providers>
   <add name="SQLRP" connectionStringName="SqlAuth" applicationName="/" type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.50727.4927, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
    </providers>
   </roleManager>
  </system.web>  

7.      Permissions for the User

The Site Collection Administrator is under Site Collections in Application Management
 Choose the Site URL and type the Created User



Click Ok

8.      Logging into site Using FBA user
Select Forms Authentication

Login using the User created in IIS



Last but not the Least…!
I would like to thank Mr. Mukund and Mr. Donal Conlon for their help in writing this article


Best Regards,
Shiva Prasad B C
SharePoint Administrator
Please do post your comments and queries.