ASP.NET question - error accessing SQL Server db from SharePoint

Bit of an overspecific question, I know, but I’m not finding any help anywhere else, so I’m hoping the Dope comes through for me.

I’m developing an ASP.NET user control in VS2008 that writes to a SQL Server 2005 database. I’ve published the control to a SharePoint 2007 MOSS page using the SmartPart add-in. The control consists of a few text fields and a submit button that writes the contents of the text fields to the database. When I test the control in the Visual Studio debugger, it works fine. When I try to submit data from the SharePoint page, I get the following error:

Last time I saw this, it was a database permissions error, but I’ve granted my site’s default application pool read/write access to my database.

Does anyone have any idea what else the problem might be? Also, how can I double-check the credentials that the SharePoint page is using to access the database, since it looks for all the world like a permissions issue? Any advice is appreciated, thanks.

I’d check several things:

  1. Authentication in IIS
  2. Check that the app pool you think is running the SharePoint site is actually running it
  3. Check that the correct impersonation (if any) is being used

One way to check what’s actually hitting the database is to run a trace against the server using SQL Server Profiler, which will give you details on queries and stored procs that are being run, including which user is running them. A quick overview of Profiler for 2005 is here: http://www.codeproject.com/KB/dotnet/SQLServerProfiler.aspx
Video tutorial here: http://sqlserverpedia.com/wiki/Using_SQL_Server_Profiler

Well, I’m one step closer, at any rate. I realized that this control is using a connection string in web.config, which isn’t copied over when you implement a control using SmartPart…all you do is copy the published ascx file, and throw the dll in the site’s bin folder. I went back and hard-coded the connection string, and now I’m getting a new error. Progress! :smiley:

Specifically, I’m getting “Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’.”. I thought that might mean that the control wasn’t passing any credentials (app pool or otherwise) to SQL Server, but even when I specify a username and password in my connection string, I get the same error.

Can you post the connection string you’re using? (With sensitive info stripped out, obviously).

Actually, if you can copy and paste the whole of the web.config file, that might help eliminate some possibilities.

Are you setting “Trusted_Connection” to false in the connection string when you add the username/password? The error you post indicates that it’s using Windows Authentication to get to the DB - if you explicitly set the username/password in the connection string, that’s SQL Security, not Windows authentication, but it will ignore it if Trusted_Connection is set to true.

Actually, the web.config isn’t copied over when the control is deployed via SmartPart. That was the initial problem I was having; the control couldn’t find the connection string. Currently, it’s hard-coded into the app in a class I named ConnectionHandler.

Setting Integrated Security to “false” in the connection string fixed the “NT AUTHORITY\ANONYMOUS LOGON” bit, but now I’m getting "Login failed for user ‘domain\username’, when I’m certain that the credentials I’m passing have access to the database.

The connection string itself is: @“Data Source=servername;Initial Catalog=databasename;Integrated Security=False;Username=domain\username;Password=password”

ETA: Now that I think about it, the username and password I’m putting in are for an Active Directory login that has rights within SQL Server. Do I need to somehow recreate the login in SQL Server itself in order to use it without Integrated Security?

As an added note, the only thing I really care about here is getting this app access to the database ASAP. At this point, I’m willing to do pretty much anything that gets this app up and running, so I’m open to any left-field suggestions that will accomplish that.

Ok, got it. Either AD logins don’t work when you turn off integrated security, or else I don’t know how to pass them properly, but either way, creating a new user with SQL Server authentication did the trick. Thanks to everyone for your responses.

Yes, the username and password in the connection refer to the SQL Server security user, not anything to do with active directory.

You’ll need to create that user within SQL Server. I use the SQL Management Studio to do it. There’s a “Security” tab, you can add users easily enough there. Give it permissions to the appropriate database.

I then usually test the connection by clicking “New Query” in the top left corner of the Management Studio window. A dialog will popup, choose SQL Server Authentication from the Authentication drop down, and type in your username/password. If that works, make sure your .NET connection string username/password is set the same, and you should be good to go.

Edit: Oops, I was typing this while you added your last reply. Glad to hear you got it working!