Vampire (3/24/2009)

---

Brian,

I guess I dig grave for my own SQL Server :crying:

I have executed this trigger on a SQL Instance .\Inst1 and now it is not allowing me to connect from my local machine. However, let me state few facts:

1. I have inserted <local machine>, My computer Name and my IP address as values into ValidIP table, and now when i connect to the Instance from my computer(the instance on which trigger is created, also resides on my computer), the trigger executes. The login am using is a SysAdmin Acct.

2. Based on the above fact, my question is : This trigger allows only connections from the computers listed in ValidIP table..right? If yes, though I inserted my computer name in the ValidIP table..why is trigger executing?

3. What does statement return : (SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)'));

Returns value as "<local machine>"; if connected from my computer OR

IP address of Local machine or computer from which connection origins??

4. I have used your tip of DAC to break this trigger barrier..it seems my DAC is also failing. I am pasting the Error output of my DAC connection. My question here is..does SQL instances support DAC Or is DAC available only for default instances.

C:\Documents and Settings\vampire>sqlcmd -S ADMIN:.\inst1 -A -dMaster

HResult 0xFFFFFFFF, Level 16, State 1

SQL Network Interfaces: An error occurred while obtaining the dedicated administrator

connection (DAC) port. Make sure that SQL Browser is running, or check the

error log for the port number [xFFFFFFFF].

Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..

Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

If DAC is available for SQL Instances also..so please based on the above output..can you suggest me some work around.

Thanks

Vampire