• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2118
  • Last Modified:

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?
0
egxis
Asked:
egxis
  • 7
  • 4
  • 2
  • +1
1 Solution
 
bharatbutaniCommented:
Try getting into the Eventviewer & look at the logs there. Login to the SQL Server & check if there is some job that is enabled to collect the trace. Disable that. If you feels someone remotely is using the profiler use the sp_who2 command & kill that process. Also view the SQL logs in the SQL Server.

Regards
Bharat Butani.

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Check if your database isn't marked as ADMIN only?
If so, just uncheck it (in Database Options).

Good luck
0
 
egxisAuthor Commented:
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?

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]

Open in new window

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
maradamCommented:
I had a simmilar issue recently. The solution was to reboot client workstations.
0
 
egxisAuthor Commented:
Rebooting the client workstation(s) has not made any difference in this case.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Please run sp_configure and post here what was returned.
0
 
egxisAuthor Commented:
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.

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

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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'
0
 
maradamCommented:
Try the following trick on client workstation: Run client network utility, (Start->Run->cliconfg.exe) change the order of network protocols, click apply, change it back and OK. Then try to login. It sometimes works.
0
 
egxisAuthor Commented:
My result for the following command:
sp_dboption 'MyDBNameHere', 'dbo use only'

dbo use only,OFF

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Well, I'm running out of ideas. It's really a weird situation.
Did you check if the database have users?
0
 
egxisAuthor Commented:
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.


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]

Open in new window

0
 
egxisAuthor Commented:
After spending significant time trawling the internet, I found the following URL, which referred a lot to the usage of setspn.exe:
http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=60&threadid=84680&highlight_key=y&keyword1=sspi

setspn itself did not help with my problem, however a nugget from Xinwei Hong, SQL Server Protocols described my problem exactly.

So, I changed the service accounts for SQL Server and SQL Agent to a domain account, restarted the service, changed it back to LocalSystem, and rebooted the server for good measure.

Problem solved - it was an old SPN token, that did not get reset due to a server crash, thus denying valid users access.
0
 
egxisAuthor Commented:
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

Open in new window

0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 7
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now