egxis
asked on
Ordinary users unable to login after server crash
My server crashed due to SQL writing a lot of trace files, which filled the server disk, and caused the server to crash.
So my first question is: How do I disable creation of Trace files - as I don't need them
After the server came back up (and another reboot thereafter) users are unable to login to SQL Server. They are valid Active Directory users, are able to login to the domain and even access a share on my database server, however SQL server denies their connection.
Admins however do not have a problem using the server.
I assume the unexpected crash had something to do with this, as the users were still able to login this morning, before the crash. I have removed and recreated their logins to the server and the relevant database.
How can I diagnose the cause of this issue and fix my problem?
So my first question is: How do I disable creation of Trace files - as I don't need them
After the server came back up (and another reboot thereafter) users are unable to login to SQL Server. They are valid Active Directory users, are able to login to the domain and even access a share on my database server, however SQL server denies their connection.
Admins however do not have a problem using the server.
I assume the unexpected crash had something to do with this, as the users were still able to login this morning, before the crash. I have removed and recreated their logins to the server and the relevant database.
How can I diagnose the cause of this issue and fix my problem?
Check if your database isn't marked as ADMIN only?
If so, just uncheck it (in Database Options).
Good luck
If so, just uncheck it (in Database Options).
Good luck
ASKER
Login to the SQL Server & check if there is some job that is enabled to collect the trace. Disable that.
No jobs that I can see. Would using Red Gate Backup possibly cause this?
Try getting into the Eventviewer & look at the logs there:
I did run the suggested SPs below to try and trim the data in the trace files. Evidently that was not sufficient.
http://www.mssqltips.com/tipprint.asp?tip=1111
If you feels someone remotely is using the profiler use the sp_who2 command & kill that process.
Nobody is remotely creating trace files
It almost seems as if it's a Kerberos issue, but then even logins from admins on the same machine should be rejected? The DC/SQL/Client(s) were all rebooted, but still have the same problem...
VMontalvao: I was unable to see the option you're referring to?
No jobs that I can see. Would using Red Gate Backup possibly cause this?
Try getting into the Eventviewer & look at the logs there:
I did run the suggested SPs below to try and trim the data in the trace files. Evidently that was not sufficient.
http://www.mssqltips.com/tipprint.asp?tip=1111
If you feels someone remotely is using the profiler use the sp_who2 command & kill that process.
Nobody is remotely creating trace files
It almost seems as if it's a Kerberos issue, but then even logins from admins on the same machine should be rejected? The DC/SQL/Client(s) were all rebooted, but still have the same problem...
VMontalvao: I was unable to see the option you're referring to?
Error, Event ID 566
An error occurred while writing an audit trace. SQL Server is shutting down. Check and correct error conditions such as insufficient disk space, and then restart SQL Server. If the problem persists, disable auditing by starting the server at the command prompt with the "-f" switch, and using SP_CONFIGURE.
Failure Audit, Event ID 18452
Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: 192.168.1.198]
Error, Event ID 17806
SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 192.168.1.198]
Error, Event ID 8355
Server-level event notifications can not be delivered. Either Service Broker is disabled in msdb, or msdsb failed to start. Event notifications in other databases could be affected as well. Bring msdb online, or enable Service Broker.
Error, Event ID 15466
An error occurred during decryption.
Also view the SQL logs in the SQL Server.
========================================
PRE-CRASH:
2008-02-08 10:59:30.26 Logon Login succeeded for user 'DOMAIN\ordinary.user1'. Connection: trusted. [CLIENT: 192.168.1.194]
2008-02-08 10:59:30.33 Logon Login succeeded for user 'DOMAIN\ordinary.user2'. Connection: trusted. [CLIENT: 192.168.1.193]
2008-02-08 10:59:30.35 Logon Login succeeded for user 'DOMAIN\ordinary.user1'. Connection: trusted. [CLIENT: 192.168.1.194]
2008-02-08 10:59:30.43 Logon Login succeeded for user 'DOMAIN\ordinary.user2'. Connection: trusted. [CLIENT: 192.168.1.193]
2008-02-08 10:59:30.43 Logon Login succeeded for user 'DOMAIN\ordinary.user1'. Connection: trusted. [CLIENT: 192.168.1.194]
2008-02-08 10:59:30.51 Logon Login succeeded for user 'DOMAIN\ordinary.user2'. Connection: trusted. [CLIENT: 192.168.1.193]
2008-02-08 10:59:32.55 spid59 Error: 566, Severity: 21, State: 1.
2008-02-08 10:59:32.55 spid59 An error occurred while writing an audit trace. SQL Server is shutting down. Check and correct error conditions such as insufficient disk space, and then restart SQL Server. If the problem persists, disable auditing by starting the server at the command prompt with the "-f" switch, and using SP_CONFIGURE.
2008-02-08 10:59:32.57 spid59 SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
POST-CRASH
2008-02-08 11:14:43.51 Logon Error: 17806, Severity: 20, State: 2.
2008-02-08 11:14:43.51 Logon SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 192.168.1.197]
2008-02-08 11:14:43.53 Logon Error: 18452, Severity: 14, State: 1.
2008-02-08 11:14:43.53 Logon Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: 192.168.1.197]
2008-02-08 11:15:37.30 Logon Error: 17806, Severity: 20, State: 2.
2008-02-08 11:15:37.30 Logon SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 192.168.1.197]
2008-02-08 11:15:37.30 Logon Error: 18452, Severity: 14, State: 1.
2008-02-08 11:15:37.30 Logon Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: 192.168.1.197]
2008-02-08 11:15:47.52 Logon Error: 17806, Severity: 20, State: 2.
2008-02-08 11:15:47.52 Logon SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 192.168.1.197]
2008-02-08 11:15:47.52 Logon Error: 18452, Severity: 14, State: 1.
2008-02-08 11:15:47.52 Logon Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: 192.168.1.197]
2008-02-08 11:16:14.79 Logon Error: 17806, Severity: 20, State: 2.
2008-02-08 10:59:30.51 Logon Login succeeded for user 'DOMAIN\admin.user'. Connection: trusted. [CLIENT: 192.168.1.197]
I had a simmilar issue recently. The solution was to reboot client workstations.
ASKER
Rebooting the client workstation(s) has not made any difference in this case.
Please run sp_configure and post here what was returned.
ASKER
Hi,
Please find the sp_configure result attached. My question still is - how could something like ordinary users not being able to login, change after a crash?
Thanks.
Please find the sp_configure result attached. My question still is - how could something like ordinary users not being able to login, change after a crash?
Thanks.
Ad Hoc Distributed Queries,0,1,0,0
affinity I/O mask,-2147483648,2147483647,0,0
affinity mask,-2147483648,2147483647,0,0
Agent XPs,0,1,1,1
allow updates,0,1,0,1
awe enabled,0,1,0,0
blocked process threshold,0,86400,0,0
c2 audit mode,0,1,1,1
clr enabled,0,1,0,0
common criteria compliance enabled,0,1,1,1
cost threshold for parallelism,0,32767,5,5
cross db ownership chaining,0,1,0,0
cursor threshold,-1,2147483647,-1,-1
Database Mail XPs,0,1,1,1
default full-text language,0,2147483647,1033,1033
default language,0,9999,0,0
default trace enabled,0,1,0,0
disallow results from triggers,0,1,0,0
fill factor (%),0,100,0,0
ft crawl bandwidth (max),0,32767,100,100
ft crawl bandwidth (min),0,32767,0,0
ft notify bandwidth (max),0,32767,100,100
ft notify bandwidth (min),0,32767,0,0
index create memory (KB),704,2147483647,0,0
in-doubt xact resolution,0,2,0,0
lightweight pooling,0,1,0,0
locks,5000,2147483647,0,0
max degree of parallelism,0,64,0,0
max full-text crawl range,0,256,4,4
max server memory (MB),16,2147483647,2147483647,2147483647
max text repl size (B),0,2147483647,65536,65536
max worker threads,128,32767,0,0
media retention,0,365,0,0
min memory per query (KB),512,2147483647,1024,1024
min server memory (MB),0,2147483647,0,8
nested triggers,0,1,1,1
network packet size (B),512,32767,4096,4096
Ole Automation Procedures,0,1,0,0
open objects,0,2147483647,0,0
PH timeout (s),1,3600,60,60
precompute rank,0,1,0,0
priority boost,0,1,0,0
query governor cost limit,0,2147483647,0,0
query wait (s),-1,2147483647,-1,-1
recovery interval (min),0,32767,0,0
remote access,0,1,1,1
remote admin connections,0,1,0,0
remote login timeout (s),0,2147483647,20,20
remote proc trans,0,1,0,0
remote query timeout (s),0,2147483647,600,600
Replication XPs,0,1,0,0
scan for startup procs,0,1,0,0
server trigger recursion,0,1,1,1
set working set size,0,1,0,0
show advanced options,0,1,0,1
SMO and DMO XPs,0,1,1,1
SQL Mail XPs,0,1,0,0
transform noise words,0,1,0,0
two digit year cutoff,1753,9999,2049,2049
user connections,0,32767,0,0
user options,0,32767,0,0
Web Assistant Procedures,0,1,0,0
xp_cmdshell,0,1,0,0
Looks ok to me.
Can you run this command: sp_dboption 'YourDBNameHere', 'dbo use only'
If dbo use only value it's on then you must set it to off, using this command:
sp_dboption 'YourDBNameHere', 'dbo use only', 'FALSE'
Can you run this command: sp_dboption 'YourDBNameHere', 'dbo use only'
If dbo use only value it's on then you must set it to off, using this command:
sp_dboption 'YourDBNameHere', 'dbo use only', 'FALSE'
Try the following trick on client workstation: Run client network utility, (Start->Run->cliconfg.exe)
ASKER
My result for the following command:
sp_dboption 'MyDBNameHere', 'dbo use only'
sp_dboption 'MyDBNameHere', 'dbo use only'
dbo use only,OFF
Well, I'm running out of ideas. It's really a weird situation.
Did you check if the database have users?
Did you check if the database have users?
ASKER
I will try your suggestion, maradam.
I removed and added all users again. The error from above that concerns me the most is below.
I don't know if this has anything to do with Kerberos or similar.
I removed and added all users again. The error from above that concerns me the most is below.
I don't know if this has anything to do with Kerberos or similar.
2008-02-08 11:15:47.52 Logon Error: 18452, Severity: 14, State: 1.
2008-02-08 11:15:47.52 Logon Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: 192.168.1.197]ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
To answer my own second question, the commands required to disable writing of trace files:
EXEC master.dbo.sp_configure 'allow updates', 1;
GO
EXEC master.dbo.sp_configure 'show advanced options', 1;
GO
EXEC master.dbo.sp_configure 'default trace enabled', 0;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC master.dbo.sp_configure 'show advanced options', 0;
GO
EXEC master.dbo.sp_configure 'allow updates', 0;
GO
Regards
Bharat Butani.