Paul's profilePaul Galvin's SharePoint...BlogListsSkyDrive Tools Help

Blog


    November 12

    Functional Example: BDC ADF that connects to SQL database with embedded user id and password

    I needed to wire up MOSS to a SQL database via BDC.  For testing/POC purposes, I wanted to embed the SQL account user id and password in the ADF.  Starting with this template (http://msdn2.microsoft.com/en-us/library/ms564221.aspx), I created an ADF that connects to a particular SQL server instance and logs in with a specific user id and password and shown in this snippet:

      <LobSystemInstances>
        <LobSystemInstance Name="ClaimsInstance">
          <Properties>
            <Property Name="AuthenticationMode" Type="System.String">PassThrough</Property>
            <Property Name="DatabaseAccessProvider" Type="System.String">SqlServer</Property>
            <Property Name="RdbConnection Data Source" Type="System.String">actual server\actual instance</Property>
            <Property Name="RdbConnection Initial Catalog" Type="System.String">actual initial catalog</Property>
            <Property Name="RdbConnection Integrated Security" Type="System.String">SSPI</Property>
            <Property Name="RdbConnection Pooling" Type="System.String">false</Property>
            
            <!-- These are the key values: -->
            <Property Name="RdbConnection User ID" Type="System.String">actual User ID</Property>
            <Property Name="RdbConnection Password" Type="System.String">actual Password</Property>
            <Property Name="RdbConnection Trusted_Connection" Type="System.String">false</Property>
    
          </Properties>
        </LobSystemInstance>
      </LobSystemInstances>
    

    It is not a best practice, but it's useful for a quick and simple configuration for testing.  This was surprisingly difficult to figure out.  I never found a functional example with search keywords:

    • adf embedded userid and password
    • embed user id and password in adf
    • embed user id and password in adf bdc
    • sharepoint bdc primer
    • sharepoint embed user id and password in adf

    </end>

     Subscribe to my blog.

    Comments (4)

    Please wait...
    Sorry, the comment you entered is too long. Please shorten it.
    You didn't enter anything. Please try again.
    Sorry, we can't add your comment right now. Please try again later.
    To add a comment, you need permission from your parent. Ask for permission
    Your parent has turned off comments.
    Sorry, we can't delete your comment right now. Please try again later.
    You've exceeded the maximum number of comments that can be left in one day. Please try again in 24 hours.
    Your account has had the ability to leave comments disabled because our systems indicate that you may be spamming other users. If you believe that your account has been disabled in error please contact Windows Live support.
    Complete the security check below to finish leaving your comment.
    The characters you type in the security check must match the characters in the picture or audio.

    To add a comment, sign in with your Windows Live ID (if you use Hotmail, Messenger, or Xbox LIVE, you have a Windows Live ID). Sign in


    Don't have a Windows Live ID? Sign up

    No namewrote:
    I get this when trying to access wiht user id and Password

    A Metadata Exception was constructed in App Domain '/LM/W3SVC/1032898172/Root-2-128851566339311184'. The full exception text is: The LobSystemInstance Property 'AuthenticationMode' has a value that requires 'Integrated Security' in the connection string
    Apr. 25
    Jan D'Hondtwrote:
    Havie struggled with LobsystemInstance connection setup too. Used your comments to check if everything worked.
    My setting differs in that i have set 'Integrated security' to false instead of SSPI
    and 'rdbconnection Pooling' to true.

    My observation about Connection Pooling: since the connection string for any user of this BDC will be exactly the same, the same connection will be used over and over again and therefore can be efficiently pooled by the system. Thus saving overhead on the sql server side. Or is there something I overlooked?
    Nov. 26
    Picture of Anonymous
    Leo wrote:
    What is the acutal instance and actual initial catalog? My guess is actual initial catalog should be the database. What is actual instance then? And if my MS SQL is using windows authentication ? what would be the user id and password in this case? domain\user then my windows log on password?
    Dec. 7
    prabuwrote:
    What is the difference between PassThrough and RevertToSelf in the Authentication mode.
    Nov. 15

    Trackbacks (1)

    The trackback URL for this entry is:
    http://paulgalvin.spaces.live.com/blog/cns!1CC1EDB3DAA9B8AA!320.trak
    Weblogs that reference this entry