Pass-through authentication for MS SQL 2005 DB from SharePoint?

Question

Within our Active Directory domain, we have a MS SQL 2005 server, and a SharePoint (MOSS 3.0 I believe) server. Both authenticate against our LDAP server. Would like to allow these authenticated SharePoint visitors to see some of the data from the MS SQL database. Primary challenge is authentication.

Any tips on getting the pass-through authentication to work? I have searched (Google) for a proper connection string to use, but keep finding ones that have embedded credentials or other schemes. I gather that SSPI is what I want to use, but am not sure how to implement.

clarification: we don't have a single-sign-on server (e.g. Shibboleth) setup yet

Answer 1

If you are using C# the code and connection string is:

using System.Data.SqlClient; 
... 
SqlConnection oSQLConn = new SqlConnection(); 
oSQLConn.ConnectionString = 
    "Data Source=(local);" + 
    "Initial Catalog=myDatabaseName;" +
    "Integrated Security=SSPI";
 //Or
 // "Server=(local);" + 
 // "Database=myDatabaseName;" + 
 // "Trusted_Connection=Yes";
oSQLConn.Open(); 
... 
oSQLConn.Close();

An excellent resource for connection strings can be found at Carl Prothman's Blog. Yoy should probably replace (local) with the name of the SQL server.

You will need to either configure SQL server to give the Domain Roles the access privilages you want. In SQL server you will need to go to Security\Logins and make sure you have the Domain\User Role (ie MyCompany\SharpointUsers). In your config you should have

Answer 2

What do you mean by "users of SharePoint"?

Do you mean that they want to see data from inside a SharePoint page? In that case you have to do impersonation in that page/application, and possible set up Kerberos correctly.

Then you will have to assign those SharePoint users (or better, their AD group) proper privileges on the SQL Server.