Solved

Host_Name returning wrong computer name

Posted on 2010-08-27
7
496 Views
Last Modified: 2012-05-10
Using SQL Server 2008:  If I run a query from Management Studio on my workstation and just say  select Host_Name() as myPC, then it correctly returns the name of my workstation.
I created a trigger on a table that uses the Host_Name() function when a record in that table is updated. If I connect to SQL server using Microsoft Access, modifiy a record in the table, then the function doesn't return the name of my workstation or even the name of the server that SQL server is hosted on. Instead it returns the name of our Terminal Server.
What's going on here?  How can I correctly identify either the workstation or login name of the user that is modifying a record?  We are not using Windows authentication. We are accessing sql server with Microsoft Access via ODBC connection.
0
Comment
Question by:BradleyCleveland
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 29

Expert Comment

by:Rich Weissler
ID: 33543850
Taking a stab in the dark -- can you confirm that your copy of MS Access is running locally, and not through citrix xenapps or anything?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33544631
>>How can I correctly identify either the workstation or login name of the user that is modifying a record?  <<
Don't rely on HOST_NAME() it is easy to change from the client.  Instead pass in the user name, computer name, IP address of the workstation or whatever.
0
 
LVL 1

Author Comment

by:BradleyCleveland
ID: 33544803
Razmus, I am running Access through my local machine. There are of course other users that are accessing the database with MS Access via TS at the same time I am using my workstation.

ACPerkins: I am trying to isolate on one particular table where a field is being accidentally changed. There are a number of forms that link to this table from the Access program and it is used by dozens of employees. On the main form for editting the data in this table, we trap the username and computer name, but the table is not reflecting this when the data is changed. It's not even supposed to be changed by any other form. There is also a slight possiblity that there is a coding erro in an SP somewhere. So the best way to find the problem is to have SQL server alert me as soon as data is changed in the table. Without being able to identify either the computer name or login name though, Its still going to be difficult to find how the data change was initiated.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 4

Accepted Solution

by:
MSSystems earned 500 total points
ID: 33545453
I have seen this before with MS Access. You will find in your data source connection, where you can actually specify the workstation name. SQL then takes this as the actual workstation. Please take a look at the data source, I hope this helps
0
 
LVL 1

Author Closing Comment

by:BradleyCleveland
ID: 33546458
Found it. Thanks so much.
0
 
LVL 4

Expert Comment

by:MSSystems
ID: 33546534
It is a pleasure
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33547711
>>You will find in your data source connection, where you can actually specify the workstation name. SQL then takes this as the actual workstation. <<
Yep.  That is exactly what I meant about not relying on HOST_NAME(), it can be spoofed to be anything.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now