• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 972
  • Last Modified:

Set the workstation ID to the username and pass it to the Audit Trigger

Following:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_20896882.html

and

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20843568.html?query=trigger+workstation&searchType=topic

I have set my workstation Id to the current logged in userID to track insert, updates, and deletes.  Thing is, when I step through the code,  I can see that it's correct.  When I chekl the audit Table, it has my machine name or machine name/instance name no matter what.  What am I doing wrong?
private MembershipUser mu;
            private string UserID;           
 
            mu = Membership.GetUser();
            UserID = mu.ProviderUserKey.ToString();
 
            ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["FI"];
            if (null != settings)
            {
 
 
                // Retrieve the connection string.
                string connectString = settings.ConnectionString;
 
                // Create a new SqlConnectionStringBuilder based on the
                // partial connection string retrieved from the config file.
                SqlConnectionStringBuilder builder =
                    new SqlConnectionStringBuilder(connectString);
 
                builder.WorkstationID = UserID;
 
                SqlConnection cn = new SqlConnection(builder.ToString());
 
                cn.Open();
                cn.Close();
            }

Open in new window

0
malibuncran
Asked:
malibuncran
  • 6
1 Solution
 
reb73Commented:
The .NET Framework library is possibly overriding the WSID attribute..

Step through the code and immediately after the connection is established, run the sp_who2 query using SQL Query Window to check the attributes for this connection..

If you have many connections, then you could run a quick query from code to find out the SPID for the current connection -

SELECT @@SPID

0
 
malibuncranAuthor Commented:
Hi, thanks for replying. I really appreciate it.  

I was able to see my 'username' as the host name for one of the returned rows.  But, according to the date stamp, seconds later it goes back to the machine or machine/windows username.  

The SPID is the same each time, so I don't need to query for that.  What else can I do to make it 'stick' to the Workstation ID I assign it.  

I've upped the points in to show my gratifications.  
0
 
malibuncranAuthor Commented:
After it connects and

51         sqlUname  'assigned uname'      dbName       04/21 13:23:16  .Net SqlClient Data Provider        
52   NT AUTHORITY\SYSTEM       machineName   ReportServer$SQL2005  04/21 13:23:18  Report Server      

This is the SPID,  Login Name (from web.config), host name, db name, last batch,  and prog name.  These two are always right behind one another.  

When the trigger runs though, the host name is not correct.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
malibuncranAuthor Commented:
Ok, I added a 'test' workstation name to the connect string and it recorded successfully.  But it still is not taking my value from the code.  Let me rearrange the code.
0
 
malibuncranAuthor Commented:
I removed the user name and password from the connection string and passed them to the the builder via the code.  The connection string im my web.config is definitely over riding my code because I am getting 'Login failed for user ''. The user is not associated with a trusted SQL Server connection. '  which is what's expected if there is no uname and pword in the connection string.
0
 
malibuncranAuthor Commented:
I figured out my problem.  I was using a code generator whose default is to use what's in the .config file.  I 'over rode' the default.  

It works now.
0
 
malibuncranAuthor Commented:
Ir lead me to my solution.  Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now