With the release of SharePoint 2010 came many significant improvements to the SharePoint platform. Among them was the release of SharePoint Designer 2010. After working with the new release of SharePoint Designer I have been *very* impressed with the improvements that have been made.
It wasn't until today that I was problem free with SharePoint Designer. It started off with beginning to work more closely with External Content Types using the Secure Store Service. Due to the amount of time spent trying to come up with a solution alone, was reason enough for me to write something about it.
Scenario: Connect to remote SharePoint site collection in SharePoint Designer 2010 to create an External Content Type from a SQL database that isn't on the same server as SharePoint.
Problem: Because I'm using NTLM authentication I am unable to connect to the database through SharePoint using Pass Through Authentication. The limitation with NTLM is that it doesn't support multiple server hops.
Procedure: First step is to create a Secure Store Application for my connection to the remote MS SQL database. The reason for this is it acts as a bypass to the multiple server hop limitation with NTLM by authenticating against the Secure Store Service using my NTLM credentials to retrieve the credentials needed to authenticate against SQL server and then having SharePoint use those credentials and authenticating against SQL server.
Because SQL server is on a different domain than my local box I decided to use SQL authentication not Windows authentication to login against SQL server. I created a SQL account that I could use for the external content type and mapped that user to my SharePoint account in the Secure Store Application.
Everything was setup to work properly...the problem was when I tried to connect to the database I was getting back an error message:
It wasn't until today that I was problem free with SharePoint Designer. It started off with beginning to work more closely with External Content Types using the Secure Store Service. Due to the amount of time spent trying to come up with a solution alone, was reason enough for me to write something about it.
Scenario: Connect to remote SharePoint site collection in SharePoint Designer 2010 to create an External Content Type from a SQL database that isn't on the same server as SharePoint.
Problem: Because I'm using NTLM authentication I am unable to connect to the database through SharePoint using Pass Through Authentication. The limitation with NTLM is that it doesn't support multiple server hops.
Procedure: First step is to create a Secure Store Application for my connection to the remote MS SQL database. The reason for this is it acts as a bypass to the multiple server hop limitation with NTLM by authenticating against the Secure Store Service using my NTLM credentials to retrieve the credentials needed to authenticate against SQL server and then having SharePoint use those credentials and authenticating against SQL server.
Because SQL server is on a different domain than my local box I decided to use SQL authentication not Windows authentication to login against SQL server. I created a SQL account that I could use for the external content type and mapped that user to my SharePoint account in the Secure Store Application.
Everything was setup to work properly...the problem was when I tried to connect to the database I was getting back an error message:
I then looked in the Secure Store Audit table in the SQL database and noticed that nothing was being logged for the requests from Designer. My next step was to close down SharePoint Designer due to a possible issue with caching. After reopening I selected the Impersonate Custom Identity option and entered the Secure Store Application ID which would return the windows identity credentials I had stored in the application. When I was prompted with a Windows login, I entered the SQL DB user credentials which worked!! When I opened the connection properties I noticed that the Secure Store Application ID wasn't what I had first entered...wierd. The secure store application ID was what I had configured to return the SQL server credentials. Bottom line...try and try again.
So, looks like there are still some kinks with SharePoint Designer 2010. If I discover any further oddities I'll be sure to update this blog.
No comments :
Post a Comment
I always welcome constructive feedback. Thanks.