Can't Connect to SQL Server from SharePoint Designer

Question

Scenario:
Windows Server 2008 running IIS 7.0
MS SQL Server 2008 R2
MS SharePoint Server 2010
MS SharePoint Designer 2010

MS SQL Server set to SQL Server and Windows Authentication
SharePoint using NTLM w/o Kerberos


Problem:
I cannot create an ASP web part (gridview specifically) within Designer that will connect to the SQL database. I can successfully create a connection string using windows authentication but, when I try to preview in a browser, I get a "web control does not allow "Integrated Security" and "Trusted Connection" error. I have tried many work arounds like changing the connection string to reflect, "Integated Security=False", "Trusted Connection=True", User ID=xxxxx Password="yyyy" all to no avail. I also just discovered that I cannot create a data source in Designer using the exact same credentials that succeeded with the connection string setup for the gridview. I don't have the infrastructure to implement Kerberos so am stuck with NTLM only. I also tried the "clearing tagMapping" workaround where you <clear/> <tagMapping/> in web.config. That didn't work either. Any ideas?

Answer 1

What do you mean that you don't have the infrastructure to implement Kerberos?  Are you not using Active Directory?  If you are, then it requires no other infrastructure to setup.  I can walk you through getting Kerberos to work.  I am able to create SQL-connected data sources from SPD and spent my whole weekend testing different methods, but all of my testing was done in a Kerberos situation, which is required for back-end data connectivity in SQL if SQL is not on the same server as SharePoint.

---

SharePoint Architect || My Blog

Answer 2

Thanks so much for the reply and please excuse the delay in responding. I was dreadfuly distracted for a few days. I have no experience setting up Kerberos but, hastily reviewed a technical article and it looked like I needed a complicated set of servers (key, etc.) but, if not then I will definitely take you up on your offer. Let me get with one of my colleagues on AD and get back to you ASAP. BTW - I am just trying to setup a standalone instance of SP and develop a dashboard in ASP.NET. I have SP installed, SQL installed and Designer installed. I may be able to point to an instance of AD running on another server. I will get back to you ASAP. Thanks again.

---

Scott Doremus, M.S., PMP Westford, MA

Answer 3

If SQL is on the same box as SharePoint, then you shouldn't need Kerberos, but you said you couldn't connect to it even with SPD being on the same box?  Are you browsing from a separate client or from the server itself?  If from a client, have you tried to render the page from the server itself?  Are you using asp:SqlDataSource in your web part?

On the Data Source, Windows Auth is not allowed, but Named Auth does work with an account created specifically in the SQL instance.  Not even Secure Store (SSO) works yet in pre-release...at least in my version.

---

SharePoint Architect || My Blog

Answer 4

Everything is on the same box. I had heard this named account scenario would work but, haven't tried it yet. Can you point me to the steps to create a named account on SQL Server? I think I selected "Windows and SQL Server Authentication" when I setup the instance of the server. Presumably at that point I would be able to use those credentials to create a gridview connection string in SPD. Correct?

---

Scott Doremus, M.S., PMP Westford, MA

Answer 5

Everything is on the same box. I had heard this named account scenario would work but, haven't tried it yet. Can you point me to the steps to create a named account on SQL Server? I think I selected "Windows and SQL Server Authentication" when I setup the instance of the server. Presumably at that point I would be able to use those credentials to create a gridview connection string in SPD. Correct?

---

Scott Doremus, M.S., PMP Westford, MA

Answer 6

You go into SQL itself using SSMS and create a login.  This creates an account that is in SQL and can be used when selecting the Named Auth method (it's not actually called this, but that's what I call it, since it has no name).

---

SharePoint Architect || My Blog