How to Change SQL Server Authentication Mode

Hello all,

I was asked to fix a connectivity problem for SQL Server 2008 deployed to test virtual machine. I was told that nobody was able to connect neither using sql server login nor windows login (neither SA account with generic password nor the local administrator of the windows server)

Since I have seen this in the past and heard about it, I knew what caused this problem.  Basically it was the result of not granting access to any log in with right credentials.  First thing I tried is to make sure that I am using a log in that has sys admin rights.  For this I followed the steps in my earlier blog post (click this link to read that blog).  Make sure to use strong password for the sql log in you just created.

Upon restarting SQL Server I attempted to use the new account I just created with no success.  I kept getting login failed message.  Then I wanted to check the SQL Server error log to see if any error message logged. In my situation I was able to locate the error logs stored at "C:\Program Files\Microsoft SQL Server\MSSQL10.Test01\MSSQL\Log"   however in your case it might be different based on installation path and version of the SQL Server.  Just by looking at the path you can tell that I am dealing with named instance installation of SQL Server and the name of the instance is 'Test01' and MSSQL10 means it's SQL Server 2008.  If you're dealing with another version like SQL 2008 R2 you would see 'MSSQL10_50' and if you're dealing with SQL Server 2012 it would be 'MSSQL11'.  And if you're dealing with default instance then instead of 'Test01' you would see 'MSSQLSERVER' in the path.

Anyway, I opened the file named 'errorlog' using notepad and started to scanning the entries.  Within seconds I found an error message stating that 'login failed because of server is configured with Windows Authentication mode but the login is sql login'.  At this moment I knew that I had to change the authentication mode to 'SQL Server and Windows Authentication mode' to gain access using the sql login I just created.

If you search the Internet you will find how to change the authentication mode during installation or changing the authentication mode using SSMS.  However I had no way of connecting to SQL server via SSMS to change the authentication mode.  But there is another way that it can be done.  Of course this is not documented anywhere in MS knowledge base since it involves registry hacking!!!.

In my case this was a test server and I was OK if I ended up messing up the server and need to rebuilt it. If you're in the same situation and willing to do the same then keep on reading.

Open the command prompt and type "regedit" (without quotation marks) and hit enter. Make sure to backup the registry before making any changes.  Then browse to the location for your SQL Server installation on the left pane.  In my case it was the named instance of SQL Server which was named 'Test01'.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.Test01\MSSQLServer

And here is the path to default instance:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQLServer

Then on the right pane find "LoginMode"  make a right click and click  on 'Modify'  then finally change the 'Value data' to 2, click ok and exit the registry editor.

Stop the SQL Server service and then restart it.  Now your instance authentication mode has been changed to 'SQL Server and Windows Authentication mode' and you can use the sql login that you created to access and configure the server.

HTH,
Bulent

Dropping Multiple SQL Server Objects in Single Line

As humans we try to find a way to work faster and efficient. As data professionals, typing less probably is another thing we want. My SQL Server tip today is about dropping multiple objects (tables, views, stored procedures, and even databases) in single drop statement. This is powerful but can be dangerous in production so please use caution. Here is a script that creates couple of tables and then drops both tables in single drop statement.

Sincerely,

Bulent

-- CREATE TABLES

USE tempdb

GO

CREATE TABLE dbo.TableT1 (t1c1 TINYINT)

CREATE TABLE dbo.TableT2 (t2c1 TINYINT)

GO

INSERT INTO dbo.TableT1 VALUES(1)

INSERT INTO dbo.TableT2 VALUES(2)

GO

-- CHECK THE TABLES CREATED

SELECT *

FROM sys.tables

WHERE name LIKE 'TableT_'

GO

-- DROP BOTH TABLES IN SINGLE DROP STATEMENT

DROP TABLE dbo.TableT1, dbo.TableT2

GO

-- CHECK THE TABLES DROPPED

SELECT *

FROM sys.tables

WHERE name LIKE 'TableT_'

GO

Cycling SQL Server Error Log

I have a daily check list that I go through everyday. And checking the SQL Server error log is one item on the list. Depends on how your server is configured SQL Server logs informational and error messages to the log file. By default your server is configured to have 6 log files that it keeps and when the SQL Server Service starts it removes the oldest log file. In the meantime if your server is up for a long time the information in that log file just keeps growing and makes the log file hard to open from remote workstation.

In the environments that I support I change the configuration so that I keep more than 6 files. I set up to store 99 files (which is the max allowed files for the SQL Server error log). Then I create a job that runs every night right after midnight. This way when I come in every morning there is a brand new log file and since the daily log files are much smaller it makes the file easy to open and check. I use the script below to change the number of log files (note you can do this using SSMS GUI and just make a right click on SQL Server Logs under Management folder than click on Configure and change the value to what you need). After the change using second script I create SQL Server Agent Job that is scheduled to run right after midnight to cycle the error log. This make the daily task of checking the error logs easier (at least for me). Use the scripts below in a test environment and once you find out it could add some value to your daily tasks you could use it in your production environment.

HTH,
Bulent

Script 1:
This changes the number of log files stored to a value you set at the end and uses extended stored procedure to update the registry. Remember this can be done through GUI but it does executed the same in the background.

USE [master]

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 60

GO



Script 2:
This script creates a job (make sure you sql server agent is running) and schedules it to run at 12:00:01 am everyday.

USE [msdb]

GO

/****** Object: Job [A DBA SQL Server Error Log Cycle] Script Date: 01/27/2012 14:01:38 ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object: JobCategory [Database Maintenance] Script Date: 01/27/2012 14:01:38 ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'A DBA SQL Server Error Log Cycle',

@enabled=1,

@notify_level_eventlog=2,

@notify_level_email=0,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N'Cycle sql serve error log and create a new error log file',

@category_name=N'Database Maintenance',

@owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [Cycle Error Log] Script Date: 01/27/2012 14:01:38 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Cycle Error Log',

@step_id=1,

@cmdexec_success_code=0,

@on_success_action=1,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=N'EXEC sp_cycle_errorlog ;

',

@database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Cycle Error Log',

@enabled=1,

@freq_type=4,

@freq_interval=1,

@freq_subday_type=1,

@freq_subday_interval=0,

@freq_relative_interval=0,

@freq_recurrence_factor=0,

@active_start_date=20111013,

@active_end_date=99991231,

@active_start_time=1,

@active_end_time=235959

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

70-433 TS: Microsoft SQL Server 2008, Database Development

Another period of time just passed very quickly and no blogs I was able to post. During that time I passed exam 70-433 TS: Microsoft SQL Server 2008, Database Development and I got another certification under my belt.

So how did I prepare for the exam? I used this book and I think it's good book that covers the exam objectives. I have also used Transcender exam to prepare for the final week. I have to warn that you need to know the XML Query in SQL Server.

Good luck if you're preparing for this exam. Soon I will start preparing for 70-450: PRO: Designing, Optimizing and Maintaining a Database Administrative Solution Using Microsoft SQL Server 2008 and if you have any tips for me please let me know.

HTH,

Bulent

Zemanta" style="border:none;float:right">