1

Forgive me as I am new to SP Dev.

I have a DB server that I use RDP to connect to via VPN from a local laptop.

I know the name of the DB server. The DB (SQL Server 2008) is set up to use Windows Authentication. Yet, when I try to connect via SharePoint Designer, I get an error stating that my credentials are incorrect. I have admin permissions to both servers.

  • Domain\Servername + WindowsAdminPasswordForComputer doesn't work
  • IP + Uname and Pass doesn't work

Am I missing something here? I also get the same kinds of issues trying to use Visual Studio to connect to the SharePoint Server.

I have read lots of articles on how to do this.

Add a database as a data source:
http://office.microsoft.com/en-us/sharepoint-designer-help/add-a-database-as-a-data-source-HA010355745.aspx

SharePoint 2010: Connecting to SQL Server Using the External Content Type Feature:
http://www.dotnetcurry.com/showarticle.aspx?ID=794

Sql Server 2008 Connection String:
http://www.connectionstrings.com/sql-server-2008

Any help is appreciated. Thank You!

Improve this question
1
  • My guess would be that the necessary ports are not open on the SQL Server. (RDP uses a different port than the SQL client.) Perhaps ask the question on stackoverflow.com.
    – Paul Schaeflein
    Jul 7, 2014 at 22:07

1 Answer 1

Reset to default
1

SharePoint newbie, prepare to go bald. Unfortunately, SharePoint just does not play with SQL as nicely as you would wish it to.

Give this a look: http://msdn.microsoft.com/en-us/library/office/ee556780(v=office.14).aspx You will want to use the Secure Store Service ("SSS") to store the username/password credentials. In return, SSS will provide you with a nickname you can the reference over in SharePoint Designer ("SPD") to create an External Content Type. While rather miserable to get working the first time around, it eventually will make sense. I'm sure others will provide a much better explanation.

By using the SSS, you separate the storage and use of the username/pw outside of SPD. One trick I'll tell you which is critical: In SPD, where it says "Name (optional)" when setting up the SQL Server Connection, supply a Name. It is mandatory. You'll see the dialog I'm talking about in the middle of http://msdn.microsoft.com/en-us/library/office/ee556780(v=office.14).aspx.

I'm sure others will provide a better answer, but this is how I got it to work for me.

Improve this answer
8
  • Thanks for the Answer Alan! Here is my question - wouldnt that content type be already set up? As this is an existing live site that is functional and I am sure the original developers had to do the same thing? Although I am looking at the Site Collection in SPD and under external content types, there are none listed - which makes me wonder how they connected to it. As for going bald - I literally emailed one of my NOC admins complaining about how i am pulling my own damn hair out.
    – Codebunnie
    Jul 8, 2014 at 18:08
  • If you use SPD to view the site, and click "External Content Types" in the left-hand pane, do you see any of the external connections displayed? If not, then it doesn't exist; at least not in that particular site collection. Either way, at that "External Content Types" window is where you can add new ECTs or edit existing ones. And when adding/editing an ECT, that's where you make use of the Target Application ID supplied by the SSS (and yes, all this hassle just to make a database connection is the cause of much hair loss). Either way, it sounds like you're close.
    – Alan M
    Jul 8, 2014 at 21:03
  • So I checked the root node in SPD - no ECTs listed. Checked subsites. No ECTs listed. I then checked Central Admin to make sure I wasnt missing any site collections. And i get even more lost. (I dont even have to pull my hair out. At this point its falling out.)In CA is see root '/' that has an addy of computerName:10000. This has a datasource listed as Wss_DbName. This is the DB i want to connect to as I assume this is where our external data is coming from since I can connect via RDP to that SQL Server box and can see said data.
    – Codebunnie
    Jul 9, 2014 at 19:30
  • When I take the above URL and paste it into a browser it maps to /Pages/MyHome. When I drill down to 'site Hierarchy' via site settings - Nothing is listed - empty list.When I load the sites/MyHome collection into SPD, I dont see any ECTs listed.Yet - when I go to our public site www.domainName.com, and look at the hierarchy - there are a crap ton of subsites, not including MyHome - pulling data from what I assume to be that same DB. I cant see this site in CA.
    – Codebunnie
    Jul 9, 2014 at 19:31
  • he public website references pages and pulls data from both the first MyHome site and its own web - yet it seems I cant figure out how to tie these two together. So i plug www.domainName.com into SPD and look for ECTS there. Still No dice. Nothing comes up. I dont understand how these are talking to eachother. If i click on the root directory in CA, and said root directory maps to a site collection, wouldnt I see all of the subsites, which would include the Domain?
    – Codebunnie
    Jul 9, 2014 at 19:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.