SQL Server - Client HostName in Networking Environment

I want to be able to track the client host name in the networking environment. The client host name is used in a trigger for specific tasks.
Windows Authentication is the connection mode applied.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nishant joshiTechnology Development ConsultantCommented:
To trigger event on specific client name at time of login, you need to create log on  trigger which is best solution for your requirement to track login from client host name.


Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
I want to be able to track the client host name in the networking environment.
So, why did you open this question in SQL Server topic?
Omer-PitouAuthor Commented:
This is about hostname tracked in a sql server trigger. Where do you think it was supposed to be?
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

nishant joshiTechnology Development ConsultantCommented:

You need to design architecture as below :

create logon trigger which will insert data to table
Another process will keep watch on that table for specific host name.if host name found trigger your process

You can use ssis package to watch on table and execute your custom process.
Vitor MontalvãoMSSQL Senior EngineerCommented:
This is about hostname tracked in a sql server trigger. Where do you think it was supposed to be?
Sorry but the Networking Environment is confusing me. Can you post the trigger code?
Omer-PitouAuthor Commented:
Here is the trigger code, every time an update is done from a client host in  a network environment or even locally when not using SSMS for update, the variable @pHostName comes empty.

ALTER TRIGGER [dbo].[contractlog] ON [dbo].[contract]
      DECLARE @pType CHAR(1);
      DECLARE @pHostName VARCHAR(50)=LEFT(HOST_NAME(),50);
      IF OBJECT_ID('tempdb..#tinserted') is not null DROP TABLE dbo.#tinserted;
      IF OBJECT_ID('tempdb..#tdeleted') is not null DROP TABLE dbo.#tdeleted;
      SELECT * INTO dbo.#tinserted FROM inserted;
      SELECT * INTO dbo.#tdeleted FROM deleted;            
      IF exists (SELECT * FROM inserted)
            IF exists (SELECT * FROM deleted)
                  SET @pType = 'U';                  
                  SET @pType = 'I';
            SET @pType = 'D';      
      EXEC dbo.trackchanges @pTable, @pPrimKey, @pType, @pHostName; /*Syntax tableName, primaryKeyField, TriggerType*/      
Vitor MontalvãoMSSQL Senior EngineerCommented:
There's no reason to be empty inside the trigger.
What about the trackchanges procedure? How it is defined?
Omer-PitouAuthor Commented:
trackChanges is a stored procedure, and the @pHostName is one of the parameters sent to that stored procedure.
When I update the table from SSMS, the hostname comes with a value, while it is empty when the update is done from my application.
nishant joshiTechnology Development ConsultantCommented:
You need to change application code to update host name while updating record.

Another solution is add trigger to table which will update host name for update command.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
Host name should always exists since it's the name of the database server.
Or something is missing in the posted code or something very weird is happening. Unfortunaly I can't see what.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.