Solved

Host_Name returning wrong computer name

Posted on 2010-08-27
7
504 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 30

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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
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…

734 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