Cannot connect to SQL Server. Login failed for user – Microsoft SQL Server, Error:18456


Inline images 1
You receive this error when you attempt to login via Microsoft SQL Server Management Studio
I was a member of the Domain Admins group and had sysadmin access to the databases. The application log in the event viewer displays the following information
Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]
 
This seems to be linked to UAC (User Account Control) and it being set high. Right clicking on MS SQL SMS and logging on as Administrator resolves this!

Website displaying a ‘Dotnetnuke Community – This site is currently unavailable. Please check back later’ message.


Image
 
A possible cause is when the SQL database cannot be accessed by the website. This can be caused when the SQL server service is restarted whilst the website was live. 
 
To resolve, it is advised that you should recycle the DotNetNukeWebsite application pool within IIS. 
 
Depending upon your configuration this is usually located in IIS under Application Pools -> DefaultAppPool-> DotNetNukeWebsite. Right click and select Recycle. After a short time the web site should then be available.

How to migrate a Microsoft SQL 7 or 2000 server instance to another server


How to migrate a Microsoft SQL 7 server instance to another server

I needed to replace the hardware that a SQL server resided on and move the SQL instance to a new server.

I started with this excellent guide as a reference point http://www.mssqltips.com/tip.asp?tip=1246

After some trial and error this is the procedure I followed.

On the existing server

  • Ensure that no users or applications have access to the databases in the SQL instance
  • Backup all the SQL databases to a folder. I used Query Analyser and a script like the one below which backs up all the databases with the exception of ‘tempdb’ to the location C:\SQLBkup

DECLARE @name VARCHAR(50), — database name

@path VARCHAR(256), — path for backup files

@fileName VARCHAR(256), — filename for backup

@fileDate VARCHAR(20) — used for file name

CREATE TABLE [dbo].#tempBackup (name VARCHAR(200), flag BIT)

SET @path = ‘C:\SqlBkup\’

— Includes the date in the filename

SET @fileDate = CONVERT(VARCHAR(20), GETDATE(), 112)

— Includes the date and time in the filename

–SET @fileDate = CONVERT(VARCHAR(20), GETDATE(), 112) + ‘_’ + REPLACE(CONVERT(VARCHAR(20), GETDATE(), 108), ‘:’, ”)

INSERT INTO [dbo].#tempBackup (name, flag)

SELECT name, 0

FROM master.dbo.sysdatabases

WHERE name NOT IN (‘tempdb’)

WHILE EXISTS(SELECT TOP 1 name FROM [dbo].#tempBackup WHERE flag = 0)

BEGIN

SELECT

@name = name,

@fileName = @path + name + ‘_’ + @fileDate + ‘.BAK’ — Unique FileName

–@fileName = @path + @name + ‘.BAK’ — Non-Unique Filename

FROM [dbo].#tempBackup

WHERE flag = 0

— does not overwrite the existing file

— BACKUP DATABASE @name TO DISK = @fileName

— overwrites the existing file (Note: remove @fileDate from the fileName so they are no longer unique

BACKUP DATABASE @name TO DISK = @fileName WITH INIT

UPDATE #tempBackup

SET flag = 1

WHERE name = @name

AND flag = 0

END

DROP TABLE [dbo].#tempBackup

  • Copy the backup directory off the server to a location that will be available to your new server
  • Power off your existing server

On the new server

  • Configure the new server to have the same drives and locations as the old one. If the databases were stored on D: and Transaction Logs on K: then ensure that the drives are exactly the same. The restore process will be looking for these when the database and transaction logs are restored
  • Ensure that the server name, domain membership and IP address are configured to be exactly the same as the old server
  • Install the same version of MS SQL matching the program file and data directory locations on the old server.  Patch as necessary
  • Stop the MSSQL Server service and instead open the SQL server in single user mode. To do this open a command prompt, use the cd command to locate the mssql\sqldata\binn directory or the folder where sqlservr.exe is located and type sqlservr –m
  • Open Query Analyser and restore the MASTER database with the following syntax replacing the location and name of the backup file to match your location

RESTORE DATABASE [Master] FROM DISK = ‘c:\sqlbkup\Master_121208.BAK’

Once the MASTER database is restored MSSQLServer service automatically shuts down.

  • Restart the MSSQLServer service and reopen Query Analyser. Restore MSDB and Model databases again with the following syntax replacing the location and name of the backup file to match your location

RESTORE DATABASE [Model] FROM DISK = ‘c:\sqlbkup\Model_121208.BAK’

RESTORE DATABASE [MSDB] FROM DISK = ‘c:\sqlbkup\MSDB_121208.BAK’

  • Once the Model and MSDB databases have been restored check Enterprise Manager to see whether user accounts and local packages have been restored
  • Now restore the user databases using Query Analyser and the syntax used for restoring the system databases. Simply replace the database name and backup location

I experienced some problems at this point as I found that my user databases had been partially created when Model and MSDB databases were restored. All the user databases were marked as ‘Suspect’ and I was unable to restore the user databases from backup because SQL server saw that the suspect databases existed but were inaccessible. To resolve this I had to delete each suspect user database in Enterprise Manager but received an error message after each deletion that the database could not be deleted. However, on restarting EM the databases had indeed been deleted and I could safely restore the user databases from backup

That should be it and the next point would be to have some key users test the system for a while before any data entry starts. Check all your settings and that all Database Maintenance plans and SQL jobs run as scheduled

Cannot access SQL Server remotely using Enterprise Manager. You receive the error ‘A connection could not be established to (SQL Server).Reason: Cannot open user default database. Login failed.’


You receive the following error when trying to access a SQL Server using Enterprise Manager

A connection could not be established to (SQL Server)

Reason: Cannot open user default database. Login failed
Please verify SQL Server is running and check your SQL Server registration
properties (by right clicking on the SQL Server node) and try again

This happened after I deleted an old database that for some reason was set as the SQL 2000 default database.

To resolve this:

· Open Enterprise Manager on the MS SQL Server

· Select Security and then Logins

· On the right hand side there is a list of the users and their default databases

· Change the default database for the users that are affected to a database that exists

How to start Microsoft SQL in Single User mode


I have used this option when I have needed to migrate a SQL server to different hardware and need to restore the MASTER database.

  • Open a command prompt
  • Type net stop mssqlserver
  • Use the cd command to locate your SQL installation ie) mssql\sqldata\binn
  • Type sqlservr –m

The server opens in single user mode. When you have finished with the single user mode stop and start the mssqlserver service to start the instance of SQL in normal mode.