SQL Error Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection when I'm trying to open the WebPart.

Question

Hello everyone,

I've created a custom Web Part for SharePoint that interacts with SQL. Everything worked fine on my DEV server.

After I moved the WebPart to the client's server I started having problems.

I get Error Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection when I'm trying to open the WebPart.

I've searched for solution for a few hours by now and everything I have found doesn't seem to work in my case.

This is how my connection string looks like:

 <add name="MyDataEntities" connectionString="metadata=res://*/MyDataModel.csdl|res://*/MyDataModel.ssdl|res://*/MyDataModel.msl;
provider=System.Data.SqlClient;provider connection string=&quot;Data Source=ServerName;Initial Catalog=DBName;
Trusted_Connection=yes;Integrated Security=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />

The SharePoint Web App with the web part and SQL DB are on two different machines.

Here's what I've tried: 1). Made sure SQL uses Mixed mode authentication 2). Made sure the account I'm using has rights to access SQL 3). Tried replacing Integrated Security=True; in the connection string with the User ID = UserID; Password=Password; where UserID and Password were the account IIS is running under.

I ran profiler while clicking on the link and it looks like the app is not using the account’s credentials and is trying to log in anonymously.

Any help is appreciated, I'm desperate because this must be up and running by tomorrow.

Thanks in advance!

Answer 1

Try SPSecurity.RunWithElevatedPrivileges: http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spsecurity.runwithelevatedprivileges.aspx

This method will run code as the ASP.Net application pool identity. Wrap your database calls with it.