July 7, 2011 at 11:30 am
Hi all,
first off I like to thank anyone who took the time to look at this. thanks!
I'm try to keep this short and sweet.
I created a trigger to track logon attempts. I than made a noob mistake and deleted the table before I deleted the trigger now every time I try to connect to that instance I get this error:
"Cannot Connect to [server name/instance]
Logon failed for login [server name/instance] due to trigger execution.
Changed database context to ‘master’.
Changed language setting to us_english. (Microsoft SQL Server, Error: 17892)"
I did some research and found a few articles saying use this code "sqlcmd -S LocalHost -d master -A" to establish a DAC connection then delete the trigger. when I run that command I get this error:
"HResult 0x274D, Level 16, State 1
TCP Provider: No connection could b made because the target machine actively refused it.
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not assessable. Check if instance name is correct and if SQL Server is configured to allow remote connections.
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired."
Please help!
July 7, 2011 at 12:39 pm
could you please try SQLCMD -S <servername> -E -A and let me know the results?
July 7, 2011 at 12:42 pm
Logon failed for login [server name/instance] due to trigger execution.
that says right there that A logon trigger has been put on the server and it is specifically rolling back your logon session, possibly to prevent you from using a certain username and also login with SQL Server Managmeent Studio; there are a lot of sample logon triggers like that here on SSC.
you probably want to post your logon trigger here for a peer review so we can determine what might be wrong with it.
it might be you need to grant priviledges to public on an audit table or something like that.
Lowell
July 7, 2011 at 1:07 pm
C:>sqlcmd -S CRPHSQL -E -A
HResult 0xFFFFFFFF, Level 16, State 1
SQL Server Network Interfaces: An error occurred while obtaining the DAC port. Make sure the SQL Browser is running, or check the error log for the port number [xFFFFFFFFFF].
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections.
Sqlcmd: Error: Microsoft SQL Server native Client 10.0 : Login timeout expired.
I checked SQL Server Configuration Manager to see if SQL Browser was running, it is not. None of the services are running SQL Server(SQLEXPRESS), SQL Server Agent, or SQL server browser. i do not have the option to start SQL Server Agent or SQL server browser and when i try to start SQL Server(SQLEXPRESS) i get a time expired error.
July 7, 2011 at 1:14 pm
since this is express, can you find the link to SSMS and right click...run as administrator? does that get you in so you can disable the trigger?
Lowell
July 7, 2011 at 1:24 pm
Yes, i know there is a login trigger i created it. the problem is i dropped a database "AuditDatabase" before i deleted the trigger now i am unable to logon to that instance after that.
Here is the code i used to created the trigger.
"Create Database AuditDatabase
Go
Use Auditdatabase
Go
Create table dbo.restrictedlogons
(loginID sysname not null,
timestamp datetime not null)
go
Use Master
Go
Create Trigger trg_track_logons
on ALL server
With execute AS 'sa'
for LOGON
as
begin
if original_logon () = 'thirdshift' and
datepart(hh, ggetdate()) between 8 and 17
begin rollback
insert auditdatabase.dbo.restrictedlogons
(loginID, timestamp)
values (original_login(), getdate())
end
end
go
July 7, 2011 at 1:26 pm
No, same error.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply