SQL Sever - Dedicated Administrator Connection - DAC

The SQL Server Dedicated Administration Connection (DAC) once again saved the day! A bad logon trigger on a development SQL Server caused all Logons to go into ROLLBACK. In effect , the Logons were authenticating but at the point of creating a user session the Logon would ROLLBACK. 1) The DAC is only allowed on the local server. Remote connections are not allowed except if configured with the sp_configure. For local server access , you’ll either need to connect locally or use RDP. If you can't RDP logon because other users logged on read How to View Remote Desktop Sessions... Read more →

SQL Server – Restrict SQL Server Logons by IP with EVENTDATA and SQL Logon Trigger

SQL Server EVENTDATA function and SQL Logon Triggers are an effective method to restrict attempted logons from specified IP addresses. A server owner requested a solution to restrict access from certain IP sources. By creating a Restricted IP table – the Logon Trigger executed a check against the table and could ROLLBACK the logon attempt. EVENTDATA returns information about server and database events. The EVENTATA function can also be executed within a Logon Trigger. The Logon Trigger executes a stored procedure in response to the LOGON event. Once the logon has authenticated but before the user session. Information returned by... Read more →

SQL Server recompile error after upgrade from 2000 to 2005

Upon upgrading a SQL Server 2000 to SQL Server 2005 Standard Edition instance – this error message appeared on a SQL Server trace error event: Msg 2801, Level 16, State 1, Procedure myTrigger, Line 13 The definition of object ‘myTrigger’ has changed since it was compiled. The statement has been terminated. Using a trace I isolated the trigger which caused this error. The developers had created the trigger in the following format: Alter table appEvent DISABLE Trigger ALL Alter table appEvent ENABLE Trigger ALL When commenting out the DISABLE TRIGGER ALL and ENABLE TRIGGER ALL the code works without error... Read more →