Solved

Host_Name returning wrong computer name

Posted on 2010-08-27
7
497 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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
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…

911 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

21 Experts available now in Live!

Get 1:1 Help Now