November 28, 2011 at 7:38 am
Right, and I can see that as OK with a web application or middle-tier application where source code executable is more likely to be inaccessible but I have to admit being uncomfortable with the idea when it's a desktop application where a debugger/tracer could be used for unscrupulous purposes and client-side code tampered with to give a different identity. Hence the desire to try and maintain the identification on the server side but it doesn't seems possible given current SQL Server's capability for such cases?
November 28, 2011 at 8:45 am
The state of tracing shared users isn't great.
On the client side, I wasn't thinking of an altering code but more something like a "@debug=1" passed into procs as an additional param which allows either logging to occur on the server, or it just allows a filter when looking at calls in the DMVs and tracing the batch.
November 28, 2011 at 11:21 am
Ah, I see what you're going at. My apologies for not being clear but in this case, I'm thinking more about the timestamps of the record than for debugging purposes.
With a shared login, I have to "trust" the client to tell me that the user at the keyboard is the user that I think the user is. Though we could have them authenticate using a DIY procedure at the application's startup, the subsequent connections they open obfuscate whether they're the same logical endpoint, so I don't see an easy way of stamping any records server-side based on the authentication from the initial procedure when it's coming in with a different SPID.
November 28, 2011 at 3:17 pm
It's not trusting the client, but having a way to trace the data in the DMVs or Extended Events to a specific client when there's an issue.
You won't want to enable this for all users, but for specific ones when there's an issue, and you have a shared login on the server.
November 28, 2011 at 3:59 pm
Banana-823045 (10/31/2011)
I googled quickly but perhaps I don't have the right keywords. As always will appreciate pointer toward articles discussing this subject which I'm very sure has been hashed several times over.The puritanical side of me wants to insist that we should be creating SQL Login/User for each physical user, though I'm told that a common setup for several web application is to create a single login/user and manage the users within the user table (and therefore within the application, rather than the server). I can see why web application would work that way, but I'm not so convinced same would work equally well for a desktop client application.
At least, I hope to know more about the caveats and downside of using either methods so I can make more informed decisions.
Thanks!
if going the .NET client application route, it should be possible to setup and use an ASP.NET user database to store all the user information and just requires a login form to perform the authentication into the app, there would be a base account in SQL server that the app connects as
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 29, 2011 at 12:34 am
Steve Jones - SSC Editor (11/28/2011)
It's not trusting the client, but having a way to trace the data in the DMVs or Extended Events to a specific client when there's an issue.You won't want to enable this for all users, but for specific ones when there's an issue, and you have a shared login on the server.
This turns awesome in Sql 2012. See http://sqlskills.com/BLOGS/BOBB/post/Correlating-client-and-server-side-trace-in-SQL-Server-Denali.aspx
Basically, using Event Tracing for Windows (ETW), when you turn on tracing on the front end (IIS -> Asp.net -> System.Data -> Sqlncli) SQLNCLI11 will pass an activityid to sql, which will then expose it as relatedActivityId in extended events.
End-to-end tracing from the IIS page request across the network to the extended events in sql server. And you can turn it on or off at will without restarting servers or services. And it's performant.
It doesn't solve the login challenges from the OP, but for troubleshooting complex issues, it's hard to beat.
good luck.
Viewing 6 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply