Login in via intranet application and audit user changes.

Question

Login in via intranet application and audit user changes.

cmartinz

SSC Eights!

Points: 941
More actions
July 14, 2004 at 1:27 pm

#84968

Hello to all. I was wondering if anyone can help with a very easy question for those who work with Intranet applications. This the case:
1. A new application was created with .NET
2. The developer created a generic user to connect to SQL Server (the user has its own ID and password, but the connection string uses the generic user)
3. Now we have to audit all changes in the database and this cannot be done (as far as I know, I am not quite sure) because the user identified by SQL Server is the generic user (called AppUser)
4. Any changes to the database, regardless of who is logged in, is being inserted in our audit tables as AppUser. If I work directly from EM, the audit knows who did what, but the other way around.
5. I did recommend to change to: create a database role and create SQL server users assigned to that role to be able to identify who is making those changes.
6. It looks like this route is the one we are taking now.
7. The developer is going to add the connection string for each user (I think this is crazy! to be able to maintain it will be a pain)
8. I am not familiar with .NET at all
9. Is there any way (samples) I can show the developer to create the connection string using SUser_Sname function or something similar?
I will really appreciate all the help I can get in this matter (creating the SQL Server role and users is not biggy, but I think it has to be a better way to create the connection string other than one line per user, I could be wrong as many times in the past)
OH! Did I forgot to mention that this needs to be done last week (like always with this cases!!) Thank you very much in advance for your suggestions, recommendations, etc, etc, etc!!! I hope I was clear enough explaining the issue.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Shaji Venugopal Old Hand Points: 348 More actions · Answer 1

EM does that because it is using windows authentication to connect to the database. One way I can think of is to pass the userid from the client to the sp that does the update.

cmartinz SSC Eights! Points: 941 More actions · Answer 2

Thanks Shaji. Recording the changes via SQL Server is not really the problem if the users is using an account (windows or SQL Server) the problem is how to write the connection sting using a user ID variable.

The current connection string reads something like this:

Provider=SQLOLEDB.1;Password=XXXX;User ID=XXXX;Initial Catalog=d1036cy;Data Source=my-sql-server.com,44455Provider=SQLOLEDB.1;Password=AppUser;User ID=AppUser;Data Source="MyServerName"

What I need is something like:

Provider=SQLOLEDB.1;Password=@Password;User ID=SUser_SName;Initial Catalog=d1036cy;Data Source=my-sql-server.com,44455Provider=SQLOLEDB.1;Password=AppUser;User ID=AppUser;Data Source="MyServerName"

So reagardless of who is loging in, the audit triggers identify the real user (not AppUser, which is the generic user in the connection string) Thank you

Shaji Venugopal Old Hand Points: 348 More actions · Answer 3

since you have already created sql server users, did you try Trusted_connection = yes in the connection string or the one mentioned below. I have not tried this, but this way in your sp if you use SUser_SName() you should get the real user name. Hope this helps.

"Initial Catalog=database; Data Source=mysqlserver;Integrated Security=SSPI;"

JimBeamBeagle Grasshopper Points: 22 More actions · Answer 4

That will only work if the User ASP.NET (the Intranet Application) has been setup as an authenicated user with the SQL Server. You'd have to refer back to Microsoft's Best Practice documentation (sorry - can't remember all the details off-hand :whistling.

The reason that the developer has to use the generic login is that the user on the Intranet app isn't actually the user that's trying to connect to the database - hence the generic user in the connection string.

Apart from creating generic users to match your authenicated users (very messy), the only other option is to modify all your stored procs, etc to include the user id (I'm assuming that there is a login table for the app). You could then use that ID as part of your audit trails.

Hope that made some semblance of sense

Greg Burns Mr or Mrs. 500 Points: 559 More actions · Answer 5

I completely agree with what Jim above says above.

All my intranet apps (that require authentication) use a connection string like

"server=MYSERVER;Trusted_Connection=true;database=MyDB;Application Name=MyApp"

"Trusted_Connection=true" is equivalent to "Integrated Security=SSPI"

The Application Name attribute makes it easy to see in Profiler.

When you look in profiler all activity against the db will be as the ASPNET user. (Like Jim said, you have to have ASPNET user setup properly between your web server and your SQL server, this can be challenging if different servers)

To audit users you will have to pass the username as parameter to your sprocs.

That is how I do it.

Another method is to add this line to your web.config

identity impersonate="true"

This (I believe, haven't used it) will have each users connect to the DB as themselves, not ASPNET user.

See this link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskAccessingSQLServerUsingMappedWindowsDomainUser.asp

HTH,

Greg

Greg Burns Mr or Mrs. 500 Points: 559 More actions · Answer 6

That link should have been

Accessing SQL Server Using Windows Integrated Security

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskAccessingSQLServerUsingWindowsIntegratedSecurity.asp?frame=true

Greg

cmartinz SSC Eights! Points: 941 More actions · Answer 7

Thanks to all. I will try those solutions first. I think that solves the problem, but I need to test it and pass it along to the developer. Thank you very much and will come back if any question.!!