Question

  • Creator
    Topic
  • #2158041

    Application Roles in VBA

    Locked

    by dynic-it ·

    Hello

    Can someone please help. I am trying to connect to a SQL2005 database through VBA in Excel. I have set up a Application Role with permission to select from my database, and it works fine from my computer, but it doesn’t work on a regular users computer.

    I’m a full administrator, and the users computer gives a “Type mismatch” error when I try and open the connection, which makes me think that the logon is using Windows authentication and ignoring the Application Role

    Connection String:
    “Provider=SQLOLEDB;server=myserver;Initial Catalog=mydatabase;Integrated Security=SSPI;UID=myapplicationrole; Pwd=mypassword;”

    Code:
    Dim rs As ADODB.Recordset
    Dim objConnection As New ADODB.Connection
    objConnection.Open myconnectionstring
    Set rs= objConnection.Execute(“SELECT STATEMENT”)

    Thanks

All Answers

  • Author
    Replies
    • #2948197

      Clarifications

      by dynic-it ·

      In reply to Application Roles in VBA

      Clarifications

    • #2948116

      You told it to use windows authentication

      by tony hopkinson ·

      In reply to Application Roles in VBA

      with integrated security=SSPI

      Application Roles sit on top of user level security.

      It’s a way of saying User Fred when running Application MyApp can do this and that
      and when running Application MyOtherApp can do this but not that.

      Last I saw you don’t set role in the connection string, but with the sp_SetAppRole stored procedure.

      Perhaps this will help, it’s not something I’ve actually done, so I won’t be a big help I’m afraid.

      http://msdn.microsoft.com/en-us/library/bb669062.aspx

      • #2955273

        sp_SetAppRole

        by dynic-it ·

        In reply to You told it to use windows authentication

        I haven’t checked the link, but I researched the stored procedure you mentioned.

        The connection string should be “UID=;Psw=”, and then you need to run an SQL command/statement through your connection in your code:
        sp_SetAppRole ‘MyAppRole’,’AppRolePassword’

        You do however ned to give the user a security login profile, so that they are able to run the stored proc

        Thanks for your help

        • #2955161

          Still not sure on the type mismatch

          by tony hopkinson ·

          In reply to sp_SetAppRole

          Suspect that may be an old dll on the client or some such.

          try User ID instead of UID.

          A late thought…

Viewing 1 reply thread