Error:18456, Severity:14, State:58 in SQLServer

Users may encounter SQL Server login failures with below error message.

Cannot connect to “SQL Server\Instance”
Additional Information:
Login failed for user “TestUser”.(Microsoft SQL Server, Error: 18456) 

User is able to connect using Windows credentials, but not able to connect using “SQL Server Authentication” using SQL Server Management Studio and ODBC.

Let’s see what is in the ERROR.LOG

2014-01-28 23:06:46.80 Logon     Error: 18456, Severity: 14, State: 58.
2014-01-28 23:06:46.80 Logon      Login failed for user TestDB. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: xx.xx.xx.xx]

Cause: The server is configured for Windows authentication only and not able to connect using SQL Server Authentication.

Solution: Modify the Server is to use both SQL Server and Windows Authentication mode.

Right Click on Instance -- Go to properties -- Click on security – change to “SQL Server and Windows Authentication mode” – Restart the Instance

After SQL Server instance restart you should be able to connect. Sometimes users may see below error

provider: SQL Network Interfaces, error: 28- Server doesn’t support requested protocol) (Microsoft SQL Server 

Follow the below link to resolve the issue
http://www.sqlserver-expert.com/2014/01/cannot-connect-to-sql-server-or-instance.html

Thanks,
Satishbabu Gunukula
http://www.sqlserver-expert.com

Cannot Connect to SQL Server or Instance

Installed SQLServer with named instance and when trying to connect from remote host received following error

Cannot connect to “SQL Server\Instance”
Additional Information:
A network-related or instance-specific error occurred while establishing a connection to SQL Server.
The Server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 28- Server doesn’t support requested protocol) (Microsoft SQL Server)

Error Screen shot: 

There are various reasons for this error, below are the main reasons

1. You might have entered an incorrect connection string – double check that the connection string is correct or not. If you are using the IP address to connect then make sure that you are able to ping the IP.

2. Instances /Server is down , not yet started- Check SQL Server , Agent, SQL Full-Text Filter Daemon Launcher, SQL Server Browser services are up and running. If not running start the services

3. Remote connection on the SQL Server Instance was not enabled

Right Click on SQServer Instance --> Properties -->Go to “Connections”--> “Allow remote connections to this server” should be checked

4. TCP/IP port is not enabled on the Instance

Open Microsoft SQL Server Configuration Manager--> SQL Server Network Configuration -->Protocols for “Instance”--> TCP/IP MUST be “Enabled”. If not ENABLE the same.

You should be able to connect now. If you are using the dynamic port 1433 make sure to change to Static port using below post/URL

http://www.sqlserver-expert.com/2012/07/configure-sqlserver-to-listen-on-static.html

Regards
Satishbabu Gunukula
http://www.sqlserver-expert.com 

OpenSQLServerInstanceRegKey:GetRegKeyAccessMask failed (reason: 2) in SQL Server

Installed SQLServer 2012 Express Edition with Named Instance. When trying to startup the SQL Server Agent to setup the backups, the agent failed with below error

The request failed or the service did not respond in a timely fashion.
Consult the event log or other applicable error logs for details 

When I looked into Event log I found below error message, its not much useful.

OpenSQLServerInstanceRegKey:GetRegKeyAccessMask failed (reason: 2). 

I looked into the SQLAGENT log and found below error messages

2014-01-28 09:33:07 - ? [100] Microsoft SQLServerAgent version 11.0.2100.60 (X64 unicode retail build) : Process ID 1848
2014-01-28 09:33:07 - ? [495] The SQL Server Agent startup service account is SQL-Domain1\SQL-SER1$.
2014-01-28 09:33:09 - ! [298] SQLServer Error: 229, The EXECUTE permission was denied on the object 'sp_sqlagent_update_agent_xps', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (DisableAgentXPs)
2014-01-28 09:33:09 - ! [000] The EXECUTE permission was denied on the object 'sp_sqlagent_update_agent_xps', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229)
2014-01-28 09:33:09 - ? [098] SQLServerAgent terminated (normally)

I have changed the SQL Server Agent logon account to administrator and ran below command to enable AgentXPs option and restarted the SQL Server Agent, but still no luck

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO
sp_configure 'show advanced options', 0;
GO

When I looked into SQLAGENT log and found out that SQLServer Express edition does not support SQL Server Agent. 

2014-01-28 10:00:31 - ? [100] Microsoft SQLServerAgent version 11.0.2100.60 (X64 unicode retail build) : Process ID 1684
2014-01-28 10:00:31 - ? [495] The SQL Server Agent startup service account is SQL-Domain1\Administrators.
2014-01-28 10:00:31 - ? [000]
2014-01-28 10:00:31 - ? [101] SQL Server SQL-SER1\INSTANCE1 version 11.00.2100 (0 connection limit)
2014-01-28 10:00:31 - ? [102] SQL Server ODBC driver version 11.00.2100
2014-01-28 10:00:31 - ? [103] NetLib being used by driver is DBNETLIB; Local host server is SQL-SER1\INSTANCE1
2014-01-28 10:00:31 - ? [310] 4 processor(s) and 8192 MB RAM detected
2014-01-28 10:00:31 - ? [339] Local computer is SQL-SER1running Windows NT 6.2 (9200)
2014-01-28 10:00:31 - ! [000] This installation of SQL Server Agent is disabled. The edition of SQL Server that installed this service does not support SQL Server Agent.
2014-01-28 10:00:31 - ? [000] Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457)
2014-01-28 10:00:31 - ? [098] SQLServerAgent terminated (normally)

I forgot that I was working on SQLServer Express Edition...:) I hope this post is useful for users, who face  similar situation.

Regards
Satishbabu Gunukula
http://www.sqlserver-expert.com

Backup and Restore the database using WITH MOVE option

By using this option you can move a Database to new directory, location or drive. You can use either T-SQL or SQL Server Management Studio (SSMS) to backup and restore the database. When using this option you will be creating a new database at destination server and you can perform this action during online.

To backup the database use below script

BACKUP DATABASE TestDB TO DISK = 'C:\Backup\TestDB.bak'
WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Copy the Database to the Destination server and if the destination server has same driver and folder structure then you can restore the database using below script.

RESTORE DATABASE TestDB from DISK = 'C:\Backup\TestDB.bak'
WITH RECOVERY,STATS=1
GO

If you want to move the database to new folder or new drive then use “WITH MOVE” with restore command.

RESTORE DATABASE TestDB
FROM DISK='C:\Backup\TestDB.bak'
WITH MOVE 'TestDB' TO 'E:\SQLData\TestDB_Data.mdf',
MOVE 'TestDB_Log' TO 'E:\SQLLog\TestDB_Log.ldf'

You must know the logical filenames of the database in order to use WITH MOVE clause, if you don’t know then use below command to find

RESTORE FILELISTONLY
FROM DISK = 'C:\Backup\TestDB.bak'
GO

Backup and Restore the database using SQL Server Management Studio(SSMS)
You can perform the same using SQL Server Management Studio, follow the below steps

Backup the database using SSMS

1. Select Database that you want to backup, Right click and choose Tasks and Backup option
2. Provide the Backup Set Name (For ex:- TestDB_FullBackup.bak)
3. Select the Destination for Backup to as “Disk” or Tape
4. Select the file or backup device for the backup destination.
5. Click OK

Restore the Database using SSMS

1. Right client on Databases, Select Restore Database
2. Choose “From Device” and Specify the Backup media and its location of your restore
3. Select the backup sets to restore
4. Select or type the name of a new or existing database for your restore option
5. Go To “Options” tab , Modify the path under “Restore As” if you want to MOVE data and log files in different folder or drive
6. Select “Recovery State” as RESTORE WITH NO RECOVERY, If you want to apply additional logs ( Otherwise the default option will be RESTORE WITH RECOVERY)
7. Click on OK

Regards
Satishbabu Gunukula
http://www.sqlserver-expert.com

How to move SQLServer Database

For a DBA, it is one of the common activities to move the database to new folder or location or drive.

We have couple of options available to move the database in SQL Server.

1. Backup and Restore the database by using WITH MOVE option
2. Detach and Attach database using SQLServer Management Studio
3. Detach and Attach database using Transact-SQL

If you choose any option, I always recommend running DBCC CHECKDB after you move the database to check the database is structurally consistent.

Backup and Restore the database by using WITH MOVE option

Using Backup and Restore the database option you will be creating a new database at destination server and you can perform this action during online. If the database that you are moving is a production database then I advise to stop the application and take the backup.

When you use WITH MOVE option, the destination folder or driver must be available

Do not keep Backups on the same device where database is sitting. In case of device failure you should be able to restore the database using backup. Also keeping on separate drive enhances the I/O performance for both backups and database use.

Detach and Attach database using Server Management Studio or T-SQL 

When you use Detach and attach database you are moving the database instead of copying and you must perform this task during offline only.

When you detach a database, it removes the data and transaction files from the instance. You can attach these files to any other SQL Server instance

You CANNOT detach a database if any of the following is true

1. The database is replicated and published – Before you detach you must disable publishing by running sp_replicationdboption or remove replication by running sp_remove dbreplication.
2. Database snapshot exists– Before you detach you must drop all database snapshots
3. Database is being mirrored – Before you detach remove the Database Mirroring
4. A suspect Database – Before you detach you must put it into emergency mode
5. System Database – You cannot detach a System Database from Instance

When you attach a database, all f MDF and NDF files must be available. 

Make sure that no data files or file groups are in read-only mode. If primary data file being attached is ready only then database engine assumes that database is read-only.

Regards,

Satishbabu Gunukula

http://www.sqlserver-expert.com