MOSS Forum

Ask Question   UnAnswered
Home » Forum » MOSS       RSS Feeds

Database access by a web part

  Asked By: Adrianna    Date: Feb 18    Category: MOSS    Views: 2590

I'm developing a SharePoint web part that accesses a SQL Server 2005
database. I am new to SharePoint development, so please bear with
me. We have just set up MOSS 2007 in a development environment. MOSS
and SQL Server are two separate machines. SQL Server is set up to
run both Windows and SQL authentication.

The web part needs to retrieve user-specific information, but that
person will probably not have permission to access the database.
Currently, I'm passing the credentials of an account that does have
permission via the connection string. This works, but I've read that
it isn't a preferred practice for security reasons.

Is there a better way to have a web part access the database? We
cannot grant database permission to everyone who would use the web
part, so I think I would have to use impersonation in some way.



2 Answers Found

Answer #1    Answered By: Tanisha Rowe     Answered On: Feb 18

We store all of our connection strings in SharePoints web.config
file, in the connectionstrings section.

<add name="connstringkeynamegoeshere" connectionString="data
source=SERVERNAME;initial catalog=DBNAME;uid=USERNAME;pwd=PASSWORD"
providerName="System.Data.SqlClient" />

We then refer to this in code (vb.net)

Dim Cn As sqlconnection = New sqlconnection

The account we use in this connection is a sql  Server user instead of
an Active Directory user and it has limited permission on the
database - datareader and sometimes also datawriter. Never dbowner.

Answer #2    Answered By: Sierra Lewis     Answered On: Feb 18

You have a few choices.

You can put the connection string in web.config as Bonnie noted. I would go one
step futher and encrypt the string using the aspnet_regiis utility (read about
the -pe parameter).

If you have more than one web  front-end server, you should look into using the
SPWebConfigModification object to deploy your updates. Sadly,
SPWebConfigModification will not encrypt your connection string - you have to do
that yourself.

A third approach, and the one I use, is to store the encrypted string in the
Properties property of the Farm or Web Application. This alleviates the need for
an SPWebConfigModification entry.

Didn't find what you were looking for? Find more on Database access by a web part Or get search suggestion and latest updates.