Monday, December 20, 2010

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 :-)

Monday, December 13, 2010

CNAME alias for named instances

Is it a way to use this? No one can answer so I started to setup a test environment. I will be back with this issue soon.

Tuesday, October 26, 2010

Where clasul on DATETIME column

Im chame to not know this but is there any beter way to use a where clasul on a datetime column? I was not able to figure out any other solution for the moment :-)

I would like to get data where the date is 25 okt 2010 for example.

SELECT name, date
FROM tabelname
WHERE
(DatePart(YYYY, date) = 2010 and DatePart(MM, date) = 10 and DatePart(dd, date) = 25)
order by name

Thursday, April 29, 2010

How to change the server collation in SQL 2008 cluster

I was forced to change the collation sense a crap application didn’t support Latin1 collation. First I was sure that I needed to reinstall wholes SQL server with the correct collation. But after som googling I recognized that this is possible to do if I rebuild the systemdatabases. In books online you can find the solution to run the following setup command:

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ]
/SQLCOLLATION=CollationName

But when it comes to cluster you have to know these things before running the rebuild command.
1. You have to run it from the cluster node who is holding the instace.
2. The instance name shall not be written with servername\ instansname. Only the instancename shall be here.

Setup /ACTION=REBUILDDATABASE /INSTANCENAME=xxxxxxxx /SQLSYSADMINACCOUNTS=xxxxxxxxx /SAPWD=password /SQLCOLLATION=Finnish_Swedish_CI_AS

Wednesday, April 7, 2010

Unable to start SQL service SSL encryption fails

I did get some lovely errors in the SQL server log when try to start the service

Unable to initialize SSL encryption
because a valid certificate could not be found, and it is not possible to
create a self-signed certificate.
TDSSNIClient initialization failed with error 0x80092004, status code 0x80.
TDSSNIClient initialization failed with error 0x80092004, status code 0x1.v
Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log

My environment was a two node cluster with windows 2008 and SQL 2008, SP1, CLU4. The error just appears on one of the nodes. On the first one SQL start as it shall do.
After a lot of digging I find a solution on the error. I just changed the account running SQL server. My theory is that the account has too many characters in the name. I have no true proof for this but I have seen some other stupid errors when using account over 16 characters. For example I have not been able to change the password or when add a node to a cluster it will not take the password even if I know I type the correct one. Anyone else who has experience about this? Please let me know.

Wednesday, March 17, 2010

Problem to setup a linked server from a x64 edition to 2000 32 bit edition

When I did a setup of a linked server to an old SQL 2000 32 bit server I got this error when trying to do a select on a table in the linked server. There was no problem to setup the linked server.

OLE DB provider "SQLNCLI10" for linked server "BTC_SERVER" returned message "Unspecified error".
OLE DB provider "SQLNCLI10" for linked server "BTC_SERVER" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI10" for linked server "BTC_SERVER". The provider supports the interface, but returns a failure code when it is used.

For any reason SQL try to use stored procedure that is missing. If I add this sp everything works fine for me.


create procedure [dbo].[sp_tables_info_rowset_64]
@table_name sysname,
@table_schema sysname = null,
@table_type nvarchar(255) = null

as

declare @Result int set @Result = 0
exec @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type