What says the state on logon fail Error 18456

Here is what the state means a litle more detailed.


2 and 5 -- Invalid userid
6 -- Attempt to use a Windows login name with SQL Authentication
7 -- Login disabled and password mismatch
8 -- Password mismatch
9 -- Invalid password
11 and 12 -- Valid login but server access failure
13 -- SQL Server service paused
18 -- Change password required

Problem connect to an Integration Service after SP install in SQL 2005

Many time I have had problem to connect to an Integration Service after SP 3 have been installed. The solution is to reregister the dll-s below.

%windir%\syswow64\regsvr32 "%ProgramFiles(x86)%\Microsoft SQL Server\90\dts\binn\dts.dll

%windir%\syswow64\regsvr32 "%ProgramFiles(x86)%\Microsoft SQL Server\90\dts\binn\MsDtsSrvrUtil.dll

Unable to logon to Reporting Service with a domain account

I have a couple of times recived problem to logon to Reporting Service after I have done an installation. I have now read the installation manual and finds out that the account running RS need to be trusted for delegation. I have not known that before and I have done plenty of installations without this when I know there is no need for Kerberos. Today I get really frustrated and tried to solve why it was not possible to logon to the server. It worked ok with my local admin account but not the domain account with local admin rights. I run some other reporting instances on same server and they works well so I find it really strange. In BOL I find that you must be sure that the key rswindowsnegotiate is added in the RsReportServer.config file to enable Kerberos. Hmm why not remove this then to use NTML instead. I compared the working RS instance with the new one and it was not there in the working RS, so I just removed it and restarted the RS. This did magic, no I can logon to RS. Another solution should off couse be to setup the service account in a proper way but that is another question. So to make a RS work you have to setup the service account for RS to use Kerberos or be sure to remove the rswindowsnegotiate from RsReportServer.Config file

Check nr of virtual logfiles

As we know it is a good idéa to reduce the nr of virtuall logfiles in the transaction logg. To find out databases with many VLF you can use this small script.

CREATE TABLE #databases(
FileID INT
, FileSize BIGINT
, StartOffset BIGINT
, FSeqNo BIGINT
, [Status] BIGINT
, Parity BIGINT
, CreateLSN NUMERIC(38)
)

CREATE TABLE #total(
Database_Name sysname
, VLF_count INT
, Log_File_count INT
)

EXEC sp_MSforeachdb N'Use [?];
Insert Into #databases
Exec sp_executeSQL N''DBCC LogInfo(?)'';
Insert Into #total
Select DB_Name(), Count(*), Count(Distinct FileID)
From #databases;
Truncate Table #databases;'

SELECT *
FROM #total
ORDER BY VLF_count DESC;
DROP TABLE #databases;
DROP TABLE #total;

Login failed for user 'xxx\xxx'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.

We have done a huge migration work for a customer and sometimes I have seen this error after move of databases when the clients shall connect to the database on the new server.

Often when you see infrastructure error the windows log is logging as a madman. In those cases it has not. So it came to my mind that is must be something in the database. Sense we did not have any strategy to use old user and groups they often will exist in the database without any function. Often this is not a problem but there is one circumstance when it does.

This is when it has been used local groups on the source server. For example if I setup a new domain group and add user xxxx to this group and configure the necessary rights on the target SQL server/database for it this would work in normal circumstances. But not in this case. Suppose that this user xxxx was also a member of the old local group. When the user logon to the SQL server the security mechanism funds the user in this old group that still exist in the database and this would throw the error “Token-based server access validation failed with an infrastructure error”.
The solution is simple. Just delete the old group from the database :-)