none
Default suser_name() with sa login RRS feed

  • Question

  • Hi,

    I am working on adding new columns such as CreatedBy on some tables in SQL server with default value of suser_sname().  The SQL Server part of it works fine.  The problem I am facing is front-end.  Our front end application is based on Asp.Net 4 MVC and uses it Entity Framework.  Within the main config file we have connection strings to a database such as:

    <register type="ProjectName.Configuration.ISqlConnectionConfiguration" mapTo="ProjectName.Configuration.SqlConnectionConfiguration">
          <property name="ConnectionString1" value="metadata=res://*/Business.Data.db1.csdl|res://*/Business.Data.db1.ssdl|res://*/Business.Data.db1.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=SQLSERVERNAME;initial catalog=DBNAME1;persist security info=True;user id=sa_login_user;password=PASSWORD;multipleactiveresultsets=True;App=EntityFramework&quot;" />
          <property name="ConnectionString2" value="metadata=res://*/Business.Data.db2.csdl|res://*/Business.Data.db2.ssdl|res://*/Business.Data.db2.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=SQLSERVERNAME1;Initial Catalog=DBNAME2;Persist Security Info=True;User ID=sa_login_user;Password=PASSWORD&quot;" />
        </register>

    The above on it's own works fine, but not with the new requirement.  When the row is added to the table the CreatedBy column shows "sa_login_user" as a value.  I understand that this is happening because the User Login and Password is hard coded in the connection string.

    My questions are:

    To enable for suser_sname() default value work is it the only to make Windows authentication rather than sa authentication?

    The windows authentication cannot be done in our app because there are thousands of external customers that don't need User creations in SQL Server databases.  Is there any other way?  Or the only way either Windows Authentication or do it in app code?

    Thanks

     
    Wednesday, August 12, 2015 1:52 PM

Answers

  • I am working on a ASP.NET MVC using WebAPI  solution that is N-tier for our customers. It has  public facing components for clients outside the firewall (Internet),  and internal facing  components for administrators of the solution sitting behind the firewall on the LAN.  

    In either case, a email address as the user-id,  password and a string Guid  column are columns in the User table, and the Guid assigned to the user is passed down into the BLL and DAL where the DAL objects using the user's Guid identification can be persisted to the database tables for create and update and things written to audit tables . Who is doing what is known at all times.

    You have painted yourself into a corner that is going to be tough for you to correct and get out of the corner.

    Friday, August 14, 2015 1:49 PM

All replies

  • 1) You never use the SA SQL Server Admin account -- ever -- in a connection string with a solution being exposed to the public Internet

    2) You have two types of authentication that can be used for SQL Server authentication.

        a) user authentication  via user-id  and PSW

        b) Windows authentication 

    2) You must use a generic user-id and psw in the connectionstring  option a, and those credentials are presented to MS SQL Server to be used on the behalf of the external client, and the generic credentials must have the power to do CRUD operations with the database tables in question.

    3) You can forget about using windows authentication  and some default value to be implemented for the CreatedBy column.

    4) The user must be authenticated up at the UI. I am sure you have seen it by using the user's email address and psw that is  kept in a user table as an example. You either pass the email address down to the CreatedBy column or you pass the GUID that is kept in the User table linked to the user's email address and pass it to the CreatedBy column in the table.

    Wednesday, August 12, 2015 4:15 PM
  • Thanks for your reply.

    Can I confirm few points please.  

    On the above point 3, are you saying I should not use windows authentication, right?

    Point 4. Yes, the users are authenticated through email and password, which are stored in User table.  Does you suggestion mean I should take the email, for instance from the User table and insert into CreateBy column of another table using coding in C#?  This then means there is no default value in SQL table?

    I was really looking ideally not to change the code but introduce the new columns on SQL Server without deploying the app again.  Is this not feasible then?

    Thank again

    Wednesday, August 12, 2015 4:37 PM
  • On the above point 3, are you saying I should not use windows authentication, right?

    If a user on a machine is not part of the Windows domain on the LAN behind the firewall,  their machine is out there on the Internet, then they are not part of your Windows domain. So you can't use Windows Authentication to trap on the user's credentials on the back-end where SQL Server table column based on Windows authentication can be applied to a table column as the default credentials.

    Point 4. Yes, the users are authenticated through email and password, which are stored in User table.  Does you suggestion mean I should take the email, for instance from the User table and insert into CreateBy column of another table using coding in C#?  This then means there is no default value in SQL table?

    What it means is that each user in the User-Table gets  a Guid  created and placed   in a table column called User-id, UserGuidID or something. You get that GuidID from the table column for the logged-in user by email address, and you send the GuidID to the back-end to persisted to the CreatedBy column and the record persisted to the  database.

    I was really looking ideally not to change the code but introduce the new columns on SQL Server without deploying the app again.  Is this not feasible then?

    You took the wrong path, and now you have to back up and correct it.

    Wednesday, August 12, 2015 5:47 PM
  • Hi,

    To be honest I still don't understand it, especially on Guid side of it.  I think I might drop that functionality altogether as it seems quite complex to implement and only introduce Date columns, such as DateCreated, DateUpdated, which can easily use default values without any authentications.

    The application itself is quite large and consists of a number of projects.  And one of them is for internal staff only and this is where I wanted to introduce UserName values for tables.  So in this case as I can see there are 2 options:

    1. To use Windows authentication for the internal users only (staff) and create User for each in SQL server db.

    2. To use C# code within the application and pass the User values to Entities.  This can be done by simply getting current User Name from the logged in machine user.  No Guid necessary for internal staff here. But I was trying to avoid this sort of implementation as it's a bit messy and that's why were looking for cleaner solution by simply adding default User value in SQL Server.  But it turns out that's only possible with the point 1.

    Therefore, I decided to leave for now until I think of a better solution.

    Thank you for your suggestions. 

    Friday, August 14, 2015 9:18 AM
  • I am working on a ASP.NET MVC using WebAPI  solution that is N-tier for our customers. It has  public facing components for clients outside the firewall (Internet),  and internal facing  components for administrators of the solution sitting behind the firewall on the LAN.  

    In either case, a email address as the user-id,  password and a string Guid  column are columns in the User table, and the Guid assigned to the user is passed down into the BLL and DAL where the DAL objects using the user's Guid identification can be persisted to the database tables for create and update and things written to audit tables . Who is doing what is known at all times.

    You have painted yourself into a corner that is going to be tough for you to correct and get out of the corner.

    Friday, August 14, 2015 1:49 PM
  • Hi, Thank you for your reply, I just thought to give a second try.  

    We have a very similar setup, email address as the user-id, password in User table.

    I have a good understanding of the Guid column in my User table, which we don't have at the moment, but I suppose I can easily add them and manually, through SQL command, populate them. 

    But I am still failing to grasp the overall idea, especially on how the Guid assigned to the user is passed down into the BLL and DAL.  Does this mean I need to explicitly change C# code in front-end to do that?  I would rather not to do that and was hoping to somehow avoid any code changes within the front-end.

    Not sure either what Guid identification can be persisted to the database table for create and update...

    Thanks for your patience

     

    Wednesday, September 9, 2015 2:53 PM
  • But I am still failing to grasp the overall idea, especially on how the Guid assigned to the user is passed down into the BLL and DAL.  Does this mean I need to explicitly change C# code in front-end to do that?  I would rather not to do that and was hoping to somehow avoid any code changes within the front-end.

    Methods in classes can have parms passed into them, which the Guid user identification is passed to the methods as a parm. So yeah, you have to make some coding changes to pass the user Guid ID into methods that are going to pass it along to a method that is eventually going to do something with the Guid.

    Not sure either what Guid identification can be persisted to the database table for create and update...

    A Guid can be converted to a string value using the ToString() function,  and it is the string equivalent written to a Varchar column  call Userid, Who or whatever you want to name the column.

    Wednesday, September 9, 2015 3:29 PM
  • Thanks again for your reply.  I see what you mean now.  Because I will need this for only internal users/clients who are already logged in on company machines, then I suppose this can also be done using the 

    System.Security.Principal.WindowsIdentity.GetCurrent().Name

    I can then insert this into the User table.

    But I see what you mean and your solution would be an ideal if I also wanted to use it for external clients too.

    Wednesday, September 9, 2015 4:02 PM