Sql – Pass-through authentication for MS SQL 2005 DB from SharePoint

sharepointsql server

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

Best Answer

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

Related Solutions

Sql – How to return only the Date from a SQL Server DateTime datatype

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))

for example

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

gives me

2008-09-22 00:00:00.000

Pros:

No varchar<->datetime conversions required
No need to think about locale

Sql – How to concatenate text from multiple rows into a single text string in SQL Server

If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.

I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily.

If there is a table called STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Result I expected was:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

I used the following T-SQL:

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH ('')
            ) [Students]
        FROM dbo.Students ST2
    ) [Main]

You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don't need to do a sub-query:

SELECT DISTINCT ST2.SubjectID, 
    SUBSTRING(
        (
            SELECT ','+ST1.StudentName  AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH ('')
        ), 2, 1000) [Students]
FROM dbo.Students ST2

Best Answer

Related Solutions

Sql – How to return only the Date from a SQL Server DateTime datatype

Sql – How to concatenate text from multiple rows into a single text string in SQL Server

Related Topic