Thursday, November 23, 2006

Using Single Signon with Database Credentials

This one was a real puzzle. I was able to get a BDC application working using RevertToSelf credentials but wanted to use a secure database account so that thousands of users didn’t have to have accounts in SharePoint. Instead, all domain users would access the BDC LOB using a dedicated SQL Server account.


Obviously the Single Signon was required but the SDK documentation and samples are, how to put it – a little vague? Not much information is available on the MegaHyperIntraWeb either. Eventually I got it working with some help from a fellow programmer, Christopher Bowman. Hopefully these steps will help others. Please send me some feedback on your experiences with this!



1. First, make sure you can connect to your BDC Line-Of-Business app using PassThrough or RevertToSelf authentication first, even if it’s to one column from a single table, just to be sure that your BDC metadata is correct.
2. Enable the Single Sign on service. Go to Services and set Microsoft Single Signon to start automatically. Give it a domain account as an identity.
3. Go to Operations in the SharePoint Configuration site. Click “Manage Single Signon” and sign in using the identity account in step 2.

4. Click on “Manage Server Settings” to create a new SSO database.
5. Click on “Manage settings for enterprise application definitions” to create a new Single Sign On application. The name you choose will be referred to later in the BDC application definition. For Account Type, choose “Group” so that all users will log in using the same database user id and password. Don’t select the “Use Windows Authentication” checkbox. The fields you fill out here are the credentials you need to pass to your application. In this case we are trying to authenticate to our LOB application’s SQL Server database using the connection string, so “Field 1” is called “User ID” and “Field 2” will be “Password”.

6. Still in the “Manage Single Signon” section, go to “Manage Account Information for enterprise application definitions” and select the application you just created. Put “\Domain Users” as the group that will try to authenticate using SSO. Finally, fill out the User ID and Password that you will connect to the LOB database with. Make sure this account exists in the LOB SQL Server database and has rights to the particular LOB db. I think it only needs Public rights but if you are connecting to stored procs using the BDC you may need execute permissions. If you get stuck try making this account a db owner of the LOB database.
7. Now it’s time to change your BDC application definition to use SSO and database credentials. Here are the settings you need:
<LobSystemInstance Name="LOB_INSTANCE_NAME" >
<Properties>
<Property Name="AuthenticationMode" Type="System.String">RdbCredentials</Property>
<Property Name="DatabaseAccessProvider" Type="System.String">SqlServer</Property>
<Property Name="RdbConnection Data Source" Type="System.String">Name of your db server</Property>
<Property Name="RdbConnection Initial Catalog" Type="System.String">Name of your LOB database</Property>
<Property Name="RdbConnection Integrated Security" Type="System.String">false</Property>
<Property Name="RdbConnection Pooling" Type="System.String">true</Property>
<Property Name="SsoApplicationId" Type="System.String">This is the name of the SSO Application you created in step 5</Property>
<Property Name="SsoProviderImplementation" Type="System.String">Microsoft.SharePoint.Portal.SingleSignon.SpsSsoProvider, Microsoft.SharePoint.Portal.SingleSignon, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c</Property>
</Properties>
</LobSystemInstance>

There are a couple of gotchas here: Note that the type name and namespace of the SsoProvider is Microsoft.SharePoint.Portal.SingleSignon ... all the examples show Microsoft.SharePoint.Portal.SingleSignOn with a capital “O” but this is incorrect and you will receive an error about type name not being found if you don’t change this.
The other gotcha is to disable SSPI. I do this explicitly here by setting “RdbConnection Integrated Security” to false but by default it is false anyway.
8. Upload the modified BDC application definition, remembering to increment the version number first.
9. Manage the permissions of your BDC Application, giving the “Domain Users” group “Execute” permission to the BDC. You may have to add the group first.
10. Manage the permissions of each your BDC Application’s entities, making sure that the “Domain Users” group is added and has “Execute” permissions here as well.
11. If you are already searching on your BDC Application, do a full crawl of that content source again so it updates the security permissions to prevent security trimming.
12. To test, try viewing a BDC web part using any account that does not specifically exist in the LOB SQL Server Database...if you can still view the data, the Single Signon worked!

kick it on SharePointKicks.com

20 comments:

  1. Hi, thanks for this info. We are trying to get BDC working on a MOSS Web Site that is configured for anonymous access. For the life of me I cant work out what account to map to, whether it is a user or group to map to etc. I have tried using the nt authority\anonymous logon account, and mapping this to a domain account that has permissions in the SQL database, but I get the red 'cant connect' error!!

    ReplyDelete
  2. Hi Philip, are you trying to use Database Credentials and Single Sign-on? Have you been able to connect at all using PassThrough or RevertToSelf?

    You can try checking what identity the Business Data Catalog's application pool is using to authenticate - I think the pool you're looking for is the one that the Shared Service Provider uses.
    You could also try adding logins for IUSR_ and IWAM_ and giving them datareader permissions to your application's database.

    ReplyDelete
  3. Hi Nick,
    Thank you for the post. It helped me out a lot with trying to figure out how to use SQL authentication with BDC.

    The article rocks! :-)

    ReplyDelete
  4. Hi Nick,
    I am the same user that said this article rocks :-)
    I have a question - In my organization, we have three domains - SDomain, Prod and Dev. I need to configure my Enterprise application to let users of all three domains use the user id and password information for SQL Auth, can I do this?

    On the "Manage Account Information for an Enterprise Application Definition" page, I can enter only one group in the form of "Domain\group name". Is there a way to specify more than one group here?

    Thanks in advance for your time.

    ReplyDelete
  5. Thanks for a good post. Step 9 and 10 requires allowing 'domainusers' 'execute' access to the BDC. What happens if I am using ASP.NET membership and role database? I don't seem to be able to assign a role 'execute' access, though I can assign individual users. However that would be too tedious.

    ReplyDelete
  6. Thanks for this great post! This information was invaluable to us and impossible to figure out or find elsewhere.

    ReplyDelete
  7. A very good article on SSO and database access.

    ReplyDelete
  8. Hi anonymous(es?), sorry for my(very!) slow response to your questions...
    For the multiple domains - could you look into something like Federated Identity to amalgamate all your groups into one? This might make your life easier.

    As far as using .NET membership through the provider, I'm afraid I haven't tried using roles instead of groups. I have used LDAP authentication before but not with the BDC so I'm afraid I can't help with that question. Sorry.

    ReplyDelete
  9. I've tried to follow your steps to configure a SSO. But after I try to finalize my configuration my system said "I do not have the right to configure" I am actually using sharepoint admin account to configure this. Is there any reason the SSO configuration wld fail besides administrative rights problem?

    ReplyDelete
  10. Hi Leo, the SSO database is its own database, so whatever account you are using (in this case the SP Admin) needs to have DbSecurity and DbCreator rights for that database.

    You can also try resetting the service account in Central Administration under Security Configuration > Service Accounts just in case SharePoint "got confused".

    ReplyDelete
  11. This comment has been removed by the author.

    ReplyDelete
  12. I had the same issue but was able to resolve it by changing the permissions on the bdc application from within Central Admin. I was connecting to a web service though, not a db.

    ReplyDelete
  13. Thanks for that, Jendeeda! What specific permissions did you have to set for the web service account to make it work?

    ReplyDelete
  14. Hi,
    I have one site , now I want to integrate OWA (outlook web access) and PWA (Project Web Access) in my site. there is link will redirect me to OWA or PWA site.

    I have configure SSO from central admin.
    now what should I do so that, when I click on Mysite link "OWA" then it will open OWA site without asking me username and password.
    if anyone has sample code to authenticate url using SSO.

    I dont have any idea what next after configure sso from Central Admin. I want to open OWA from my site without asking me username and password again...

    I am using AD,and site will be used in intranet and internet also..
    I also itegrate sharepoint other sites all are in same domain.


    Thanks in advance.
    Jayesh Prajapati.
    jayeshgt@gmail.com

    ReplyDelete
  15. Hi Jayesh, if you configure SSO for OWA and PWA it will still ask you (and your colleagues) for your username and password the first time - this is unavoidable unless you set Group membership (which you won't want to do for OWA and PWA I'm guessing). Subsequently SSO should remember the individual username and password and won't prompt again. Is this working after the first time it prompts?

    ReplyDelete
  16. Betty is a spammer

    ReplyDelete
  17. Thanks Anonymous, time for me to update the comments again :)

    ReplyDelete
  18. Maybe you ca explain why I can use the BDC on the SSP site but my main site claims there are no BDCs in the catalog

    ReplyDelete
  19. i know this post started years ago but better late than never.....

    this is good for your application definition file xml layout
    http://httpcode.com/blogs/PermaLink,guid,648659ca-a484-4df6-85bb-8a1ddeb03b8d.aspx

    ReplyDelete

Note: only a member of this blog may post a comment.