none
Run as a different user to connection to a SQL Server database using Linq RRS feed

  • Question

  • Hi,

    I am using Linq to Entity to generate Entities from a SQL Server database. The database is Windows Authentication. Now I am encountering a issue that I do not know whether it is supported in Linq.

    I have a user A and B. A has premission to access the database. B has not premission to access the database. Unfortunally, I have to run my application on a machine that user is B. So I am not able to connect with database and generate entities via user B. Howerver, as I know, we can open Sql Server database using a different use as below:

    Now I wanna know whether this feature is supported in Linq. If yes, how?

    Thank,

    Monday, August 6, 2012 5:44 AM

Answers

  • Hello Paul,

    Unfortunately there is no options in Add ADO.Net database model wizard to specify different windows account, you can choose between Windows integrated and Mixed authentications. So here is two options:

    1) Enable mixed authentication in SQL server, which is not ggod practice from security point

    2) Run SQL Management Studio or Visual Studio under user A to configure entities and/or connnection string

    Later in your application you can supply different connection string in your application and specify user B credentials in it when calling db, something like:

    var dbcontext = new YourEntities("connectionstringtext");

    p.s. text you can copy from your web.config file if you are building something for web


    MCSE 2003: Messaging, Security
    MCITP: Server Administrator, Enterprise Administrator, Virtualization Administrator
    MCITP: Consumer Desktop Support, Enterprise Desktop Support, Enterprise Desktop Support Windows 7, Enterprise Desktop Administrator
    SMS/SCCM, ISA 2004/2006, CCNA Security

    Monday, August 6, 2012 10:44 AM
  • Just tried it on my local SQL Express

    1) If you stay with Windows integrated authentication on SQL and try to supply credentials in any way like domain\user or yourmachine\user, connection will fail. So you need to you impersonalization before creating connection to database. In that case no way supply credentials in connection string

    2) If you enable mixed security on SQL, you just need to specify credentials which you want to use in connection string


    MCSE 2003: Messaging, Security
    MCITP: Server Administrator, Enterprise Administrator, Virtualization Administrator
    MCITP: Consumer Desktop Support, Enterprise Desktop Support, Enterprise Desktop Support Windows 7, Enterprise Desktop Administrator
    SMS/SCCM, ISA 2004/2006, CCNA Security

    • Marked as answer by Alexander Sun Friday, August 10, 2012 8:55 AM
    Tuesday, August 7, 2012 4:20 PM

All replies

  • Hello Paul,

    Unfortunately there is no options in Add ADO.Net database model wizard to specify different windows account, you can choose between Windows integrated and Mixed authentications. So here is two options:

    1) Enable mixed authentication in SQL server, which is not ggod practice from security point

    2) Run SQL Management Studio or Visual Studio under user A to configure entities and/or connnection string

    Later in your application you can supply different connection string in your application and specify user B credentials in it when calling db, something like:

    var dbcontext = new YourEntities("connectionstringtext");

    p.s. text you can copy from your web.config file if you are building something for web


    MCSE 2003: Messaging, Security
    MCITP: Server Administrator, Enterprise Administrator, Virtualization Administrator
    MCITP: Consumer Desktop Support, Enterprise Desktop Support, Enterprise Desktop Support Windows 7, Enterprise Desktop Administrator
    SMS/SCCM, ISA 2004/2006, CCNA Security

    Monday, August 6, 2012 10:44 AM
  • Thank you , Sergey.

    As you said, I am able to add ADO.NET database model running Visual Studio via A now. Thank you again.

    However, I think if I need to run the application on machine B, I also need to use the feature "run as a different user". Because the database is  Windows Authentication, I am not sure I was able to config connection string to use a different windows user, am I right? If I can config to  connect with a Windows Authentication database, it will be wonderful.

    Tuesday, August 7, 2012 1:54 AM
  • Hello Paul,

    AFAIK you can use impersonalization, thats only one way to stay with integrated authentication, but have no chance to try this way. Otherwise you can provide username\password as domain\user or local_pc\user in stead of just username, which means to use SQL user instead of Windows:

    Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=domain\username;Password=myPassword;


    MCSE 2003: Messaging, Security
    MCITP: Server Administrator, Enterprise Administrator, Virtualization Administrator
    MCITP: Consumer Desktop Support, Enterprise Desktop Support, Enterprise Desktop Support Windows 7, Enterprise Desktop Administrator
    SMS/SCCM, ISA 2004/2006, CCNA Security

    Tuesday, August 7, 2012 6:14 AM
  • So if the database is only Windows Authentication, impersonalization does not work, right?

    Moreover, if the database is Windows Authentication, in connectionstring, "IntegratedSecurity" needs to set as true so that "user" and "password" will be meaningless. So I think provide domain\user or local\user won't work, right?

    Tuesday, August 7, 2012 9:04 AM
  • Just tried it on my local SQL Express

    1) If you stay with Windows integrated authentication on SQL and try to supply credentials in any way like domain\user or yourmachine\user, connection will fail. So you need to you impersonalization before creating connection to database. In that case no way supply credentials in connection string

    2) If you enable mixed security on SQL, you just need to specify credentials which you want to use in connection string


    MCSE 2003: Messaging, Security
    MCITP: Server Administrator, Enterprise Administrator, Virtualization Administrator
    MCITP: Consumer Desktop Support, Enterprise Desktop Support, Enterprise Desktop Support Windows 7, Enterprise Desktop Administrator
    SMS/SCCM, ISA 2004/2006, CCNA Security

    • Marked as answer by Alexander Sun Friday, August 10, 2012 8:55 AM
    Tuesday, August 7, 2012 4:20 PM