Post reply

Connect to SQL server 2008 from Excel/Access - with user specified userID/Pwd

  • March 6, 2012 at 10:22 am

    #265545

    I am writing an excel app to talk to Sql server 2008.

    If i use windows authentication (which uses my network ID and pwd) - it works. I give it another user who is set up in the server, it works.

    we have what we call a service account - a windows Network ID that has been authenticated to the sql server.

    I would like to connect to the server via this ID (idea is to not set up individual users on the server but let them get data via this common service account) - This is nothing fancy.

    Note: SQLLogins are not allowed and only Windows AD Ids are. I have verified that the Service account has access to the server.

    This works (this is connecting with my credentials):

    cn.ConnectionString = "Provider=SQLOLEDB; Data Source=TestServer,15001; Initial Catalog=TestDB;Integrated Security=SSPI"

    This does not (trying to connect via a gernric ID and pwd - AD ID not a sql login):

    cn.ConnectionString = "Provider=SQLOLEDB; Data Source=TestServer,15001; Initial Catalog=TestDB; UserID=MyID;Password=MyPwd; Integrated Security=false"

    Any help/suggestions is greatly appreciated.

    Thanks

  • March 6, 2012 at 11:43 am

    #1455491

    SQL server does not have windows passwords. what happens is SQL server checks the token Windows passes from Integerated security to see if you have access. SLq assumes that if you are on the network with a valid token from your domain controller, you are OK, and thus needs only to check what objects your group/windows login should have access to or not.

    that second connection string you provided is for SQL logins and passwords.

    you should be able to choose Run As... to change the user. to the other login

    http://www.sevenforums.com/tutorials/419-run-different-user.html

    if you are connecting via a programming language, you can look into impersonation, where you can use a different user programatically as well.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 6, 2012 at 12:09 pm

    #1455498

    Makes logical sense. Let me ask you this

    Here is how my security is laid out

    User >> AD wingroup >> SQL server

    Say I am logged into the PC/Windows. When I try to login to SQL Server, It passes the token to SQL server >> SQL server matches that with the wingroup set up on the server and allows the user in.

    So the guy sitting next to me, who is also in the same wingroup as i am wants to login to the app. He is not logged into my PC. In this scenario, I would like to pop up a login screen, have the user enter his windows ID and pwd and authenticate via the above path (just as I did). Are you saying this will not work ?

    (I could have used SQL login but it is not allowed at my company. I can only used Windows ID - a gernic service account that an app can use)

    RunAs works but is not a feasible solution for the Excel users i have

    Impersonation - not sure if it will for Non-Web apps like Excel

    Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply