Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2009-04-18
7
Medium Priority
?
933 Views
Last Modified: 2013-11-07
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
Comment
Question by:malibuncran
  • 6
7 Comments
 
LVL 25

Accepted Solution

by:
reb73 earned 300 total points
ID: 24187842
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
 

Author Comment

by:malibuncran
ID: 24196733
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
 

Author Comment

by:malibuncran
ID: 24196825
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:malibuncran
ID: 24196912
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
 

Author Comment

by:malibuncran
ID: 24197586
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
 

Author Comment

by:malibuncran
ID: 24219492
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
 

Author Closing Comment

by:malibuncran
ID: 31571916
Ir lead me to my solution.  Thanks.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question