Cannot generate SSPI context. (.Net SqlClient Data Provider)

Cannot generate SSPI context. (.Net SqlClient Data Provider)

The reason for this error in my case was somehow related to SPN.

The easiest first step to troubleshoot “Cannot Generate SSPI Context” is to run SQL server under Local System account and gracefully shut it down. You can then change your service account to whatever you want. SPN will not be registered and clients will fallback to use NTLM.

“Cannot Generate SSPI Context” error message, more comments for SQL Server 

Simple Concept: How to change database SQL server name in SharePoint

[Question]:

How do I change the SQL Server name in SharePoint 2010\2007?

[Answer]:

Golden rule: "User SQL Alias instead of the SQL server name"

In any case, whether you need replace the SQL instance name or SQL alias, you can do it:

For 2007: Make use of the stsadm command: stsadm -o renameserver

For 2010:  Make use of the PowerShell command: Rename-SPServer
You want to run the command on every server in the farm.

Real example:

I had the SQL alias soa-db01-prod, which it is not what I want now.
I want to replace SQL alias to soa-db05-prod.

Steps to successful SQL server name renaming on ever server that is included in the Farm (except SQL servers):
1. Add a new SQL alias "soa-db05-prod"
2. Run the command
Rename-SPServer –Identity "soa-db01-prod" –Name "soa-db05-prod"
3. Delete alias "soa-db01-prod"
4. iisreset


ATTENTION: I have discovered that after running Rename-SPServer 2 services still were referencing to the old SQL name:


1. User Profile Service Synchronization.
To fix it -  I have restarted the User Profile Synchronization Service
( to know more about  How to deal with User Profile Service - How to start User Profile Synchronization service)

2. Web Analytics Service Application
To fix it - I have stopped the services Web Analytics Data Processing Service,Web Analytics Web Service and re-created the service.

SharePoint 2010 Failover doesn't work: Login failed at System.Data.SqlClient.SqlInternalConnectionTds.LoginWithFailover

We did the SQL mirroring for a content database.Then I went on /_admin/CNTDBADM.aspx select the content database and set the failover server for it.


We have 2 servers. Initially, db01 - is a principal, db02- failover.


The connection strings looks like this one:Data Source=db01;Failover Partner=db02;Initial Catalog={Content_db_name};Integrated Security=True;Enlist=False;Asynchronous Processing=False;Connect Timeout=15'  


Then for test purposes we switch the principal to  the previous failover server(db02). db01 became a mirror.


SharePoint worked great first couple hours.


But then when we sunset db01, the SharePoint collapsed with error that he can't connect to db01:



SqlError: 'Login failed for user '{failover instance service account}.'    Source: '.Net SqlClient Data Provider' Number: 18456 State: 1 Class: 14 Procedure: '' LineNumber: 65536 Server: '{db01 server netbios name}'


SQL database login for '{Content_db_name}' on instance 'db01' failed. Additional error information from SQL Server is included below.  Login failed for user '{failover instance service account}



ConnectionString: 'Data Source=db01;Failover Partner=db02;Initial Catalog={Content_db_name};Integrated Security=True;Enlist=False;Asynchronous Processing=False;Connect Timeout=15'    ConnectionState: Closed ConnectionTimeout: 15


An error occured while attempting to verify that the database is read only for database {Content_db_name}. The error contained the following message: Login failed for user {failover instance service account}

System.Data.SqlClient.SqlException: Login failed for user '{failover instance service account}'.    
 at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)    
 at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)    
 at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)    
 at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)    
 at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)    
 at System.Data.SqlClient.SqlInternalConnectionTds.LoginWithFailover(B

Even though I can see the connection string with Failover Partner value in the log, it hasn't been recognized by SharePoint.


This strange fix worked for me:


I re-applied the failover setting on the content database through PowerShell:


***Note you must be logged into PS as the Farm Account to set the failovers, a farm admin does not have sufficient rights to do this.***
(Setting Failover Partners for Sharepoint 2010)


 $db = get-spdatabase | where {$_.Name -eq "Content_db_name"}   
 $db.AddFailoverServiceInstance("db02");
 $db.Update() 




How do you know if SharePoint uses the failover succesfully? Here is an indicator in the log file:


SQL Server failover connection detected. SQL Server db01 failing over to SQL server db02


P.S. The failover server should have a partner on SQL side. My DBA set the content db without mirroring on db02 . That immediately brought the error on SharePoint side. Somehow it checks that the failover server contains the content database that should be mirrored.


System.InvalidOperationException: Server db02, database {Content_db_name} is not configured for database mirroring


P.S.S.
There is an excellent article that covers all SharePoint 2010 databases and their failover ability. 

SimpleConcept: Message Error: 18456, Severity: 14, State: 38.

[Question]:
Message:
Message Error: 18456, Severity: 14, State: 38.
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
How to figure out what application to what database is trying to connect?

[Answer]:

State 38: Initial database could not be determined for session (SQL 2008)

The best way to investigate and locate the problem is SQL Server Profiler.

Run the trace with following trace properties:

And the filter:

You will see the ApplicationName from the request is originated.

SQL: full recovery model : full and differential backups

I have decided to put a small note regarding content database backup.

 In my life everything I do at work is related to SharePoint, that's why I have pointed out the real case for SharePoint developers; however if you are not SharePoint developer, you will get the same benefits from reading this post.
 I wanted to specify that my task is to backup  the prod content SharePoint database and to restore it on my local machine for test\developing purpose. Here is details how you can perform SharePoint content database backup\restore and why you want to do it.

 It's highly probable that you have a full recover model for the content database on Prod. That means it's highly probable that  SQL admins fortify the SQL backup strategy with  differential backups.

A differential backup backs up only modified extents since the last complete backup. The most recent differential backup contains all changes from all previous differential backups performed since the most recent complete database backup
. Differential backups have several limitations including the following:

• They do not provide point-in-time restore capabilities
• They may only be restored after a complete database backup is restored
• They may not be performed on the master database

These yellow highlights are pointing out at the fact that " IF YOU DO A FULL BACKUP WHILE DIFFERENTIAL BACKUPS ARE CONFIGURED FOR THE DATABASE - YOU ARE DISTURBING THE BACKUP SEQUENCE"
   To eliminate such effect on SQL routine, I highly recommend you to set option "COPY-ONLY" for the backup

 

Copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database.

SQL: Windows Authentication: Login with different windows account

Sometimes you logged in as one user, but you want to open SQL connection  with the different windows account using Windows Authentication.

 It's not necessary at all to login on the machine with the different account


The simple tip is run cmd. exe and type the command:
runas /user:{domain\user} "{Drive}:\{Program files}\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

OR

Press SHIFT and RIGHT click on the program that you want start under the different windows credentials.
You will get "Run as different user" option in the context menu:

Simple concept: How to find SQL Server version ?( + version table for SQL 2008)

[Question]: How do I know what version of SQL I have?

[Answer}:

Run the sql to identify the SQL Server version:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel')

, SERVERPROPERTY ('edition')

Or 

   SELECT @@VERSION;

Or look at the Object Explorer at the name of the SQL Server instance (it will be a number in the title):

You will get the version similar to this one: 10.0.4272.0

To figure out what updates are already there, refer to the following table:

Current Cumulative Update Packs for SQL 2008 R2:

Version	Service Pack (SP) / Cumulative Update (CU)
10.50.1777.0	R2 CU7
10.50.1765.0	R2 CU6
10.50.1753.0	R2 CU5
10.50.1746.0	R2 CU4
10.50.1734.0	R2 CU3
10.50.1720.0	R2 CU2
10.50.1702.0	R2 CU1
10.50.1600.1	SQL 2008 R2 RTM

10.50.2500.0

SQL 2008 R2 RTM SP1

Current Cumulative Update Packs for SQL 2008:

10.0.4279.0	SP2 CU3
10.0.4272.0	SP2 CU2
10.0.4266.0	SP2 CU1
10.0.4000.0	SP2
10.0.2816.0	SP1 CU13
10.0.2808.0	SP1 CU12
10.0.2804.0	SP1 CU11
10.0.2799.0	SP1 CU10
10.0.2789.0	SP1 CU9
10.0.2775.0	SP1 CU8
10.0.2766.0	SP1 CU7
10.0.2757.0	SP1 CU6
10.0.2746.0	SP1 CU5
10.0.2734.0	SP1 CU4
10.0.2723.0	SP1 CU3
10.0.2714.0	SP1 CU2
10.0.2710.0	SP1 CU1
10.0.2531.0	SP1
10.0.1835.0	CU10
10.0.1828.0	CU9
10.0.1823.0	CU8
10.0.1818.0	CU7
10.0.1812.0	CU6
10.0.1806.0	CU5
10.0.1798.0	CU4
10.0.1787.0	CU3
10.0.1779.0	CU2
10.0.1763.0	CU1

How to restore a sharepoint web app from a SQL backup

You may want to restore data for 2 general reasons:
1. Something terrible happened on Prod and you need to bring the system up from the backup.
(the principle to backup sql databases stays the same as for ASP.net app - the full backup once a week, and differential - once a day)
2. You need to have Staging to testing purposes (which is highly recommended!)

Whatever reason you have the process of restoring will look like this:
1. On env where you want to restore - restore the sql backup into new sql database.
2. On sharepoint env where you want to have the restored web app - go to the Central Administration - Create web application - !Choose the sql database which you have got after the first step. 
3. Create a new site collection on the top level ( it's a kind of magic for me (I took it from Restore a SharePoint Site from a Database Backup).

   It will help you to identify permission problem on SQL side. At this step you may see the error in ULS log:
 Cannot open database "[Database Name]" requested by the login. The login failed.  Login failed for user '[user_name]'. That's simply means that you have to add your application pool account to the SQL db.

4. Go to the restore site collection administrators - and check if those administrator still valid in new env, or change them as you wish to have it
5. BINGO!) You are done)
If for some reason you don't want to restore the way I describe , you are welcome to leave your suggestion and read the following related articles:

http://www.sharepointassist.com/2009/05/30/restoring-a-sharepoint-site-collection-to-a-new-domai/

http://social.technet.microsoft.com/Forums/en-US/sharepointadmin/thread/a60af1a0-caa7-4576-80a1-519ef42109db#7e5a4469-a8b8-462d-a5da-1ac0daf40adf

http://www.ungertech.com/dbrestore.html

http://www.iotap.com/Blogs/tabid/277/EntryId/136/Move-sharepoint-site-from-one-domain-to-another-domain.aspx

P.S. Experiencing the error "has been upgraded to a newer version of SharePoint. Please upgrade this SharePoint application server before attempting" - refer to the post Where to look for SharePoint version?

P.S.S. If you do such restore on the regular basis, this simple truth makes you life easier:
1. Use the same port for restored web application as you use in source (assuming, prod) web application;
2. Use the same name of db on the restored env as you use in the source environment.

SQL: An attempt was made to send an email when no email session has been established

Trying to setup job notification and keep getting the error:
An attempt was made to send an email when no email session has been established
even the Mail test is succesfull??

I know the answer!)
Seems that you having defaultDatabase Mail Executable Minimum Lifetime- 600sec
and probably your job requires more than 10 minutes mail server lifetime.
You can change the setting:
Under your sql server - Management - Database mail - Configure - view or change system settings...

But still there is a question - what if the email activity happens on the server only once a day - how we can open an email session instead keeping open the old one?

SQL: Check the last table update

 
 

select

db_name(database_id),

object_name(object_id),

last_user_update,

* from

sys.dm_db_index_usage_stats

where

object_id in

(

OBJECT_ID(N'Databasename.owner.TableName1'),

OBJECT_ID(N'Databasename. owner. TableName2')

)

order by dm_db_index_usage_stats.last_user_update desc