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

70-462 Administrating Microsoft SQL Server 2012 Databases

Hi  all,

After being quite some time and studying for exam 70-462 I would like to tell you that I passed the exam on my first attempt.  I say first because I started studying while back and things at work prevented me to allocate more time during the week and I slacked over the weekends.  And before I ran out  of time to take advantage of free second shot promotion I said just cram for several weeks and take the exam and if failed schedule the second shot free and study harder.

I used the book from Microsoft Press to study and set up a lab environment.  I have to say it was a good book to use and helpful to learn the new features.  The CD has the sample exam to test your knowledge of the  material just covered in the book.  It also has the study mode as well.  The questions in the CD exam as far as I remember were close to real exam questions.  The exam tests the knowledge of the steps to execute to properly implement something as well as writing administrative type of T-SQL statements.

For somebody with no prior experience the book is not enough to pass the exam.  However, I have been  certified in SQL Server 2008 and working with SQL Server almost 7 years when I started the real exam I thought the questions were not as hard as I expected.

I definitely recommend everybody to study and take the exam since it shows an initiative on professional development.  I am sure we can argue about the credibility and if certification count for experience.  I think that it definitely does not count for the experience but just a reminder of one's professional development and willing to learn the product.

Regards,
Bulent

SSIS Error 'Microsoft.ACE.OLEDB.12.0' Provider Is Not Registered

We have created a new laptop image which windows 7 64 bit OS with 64 bit office 2010 and 64 bit SQL Server 2008 R2 Developer edition installed along with BIDS.  After we started deploying the image and using the new configuration team members who attempted to import data from excel spreadsheet in using SSIS packages reported problems.  The error message screen shot is below.

The error message was 'The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine' when I wanted to access the sheet to see the first 200 records coming from excel file.  And when click on the columns to read the column names we got another error message which also mentions that the provider is not registered on the local machine.

Our image had 64 bit of SQL Server installed but the BIDS application is 32 bit and did not have the 64 bit driver installed for Excel so we keep getting the error.

Solution for the problem was Microsoft.  MS has a 64 bit ACE driver released which can be downloaded and installed from this link.  We have downloaded and installed AccessDatabaseEngine and opened the BIDS and SSIS package then I was able to read the data from spread sheet and get the column mappings.

HTH,

Bulent

Install .NET Framework 3.5.1 using Powershell

Hi there,
I started to study SQL Server 2012 to take certification exam and I was setting up a lab environment.  In one of the steps the exercise was to install .NET Framework 3.5.1 using powershell.  .NET Framework 3.5.1 is prereq for SQL Server 2012.

To be able to install .NET Framework the I needed to start the session elevated to administrator and then execute the powershell commands.  To accomplish this I took the following steps:

1. Right click the powershell icon on the taskbar and click on 'Run As Administrator'.
2. In the powershell window type this command 'Import-Module ServerManager' then hit enter to execute (exclude the single tick before the Import and after the ServerManager)
3. execute the command 'add-windowsfeature net-framework-core' and hit enter (leave out the single ticks around the command)

Here is the screenshot of the whole process.

HTH,
Bulent

PowerShell ExecutionPolicy Access Denied To The Registry Key

Hi all,

I recently accepted an employment offer and now working in Downtown Denver.  Anyhow, I was given a laptop as my workstation.  Since it was a brand new built laptop not everything I needed was installed so I started installing/configuring the software.  One point in time I need to start scripting and I opened the power shell window.  First thing I checked to see the execution policy setting for my new built laptop.  By default a new installation is set to 'Restricted' which does not allow to run a powershell scripts and PowerShell can only be used in interactive mode.  Which is what I expected.  So I started to configure the execution policy.  Here are the steps for how to do it.

Open powershell by clicking start>run and typing 'powershell' (without the ticks) then hit enter.  This will open powershell scripting environment.

1.  Type 'Get-ExecutionPolicy' and hit enter, this will return the execution policy setting.  There are 4 available options and I need to set the execution policy to 'RemoteSigned' to be able to run the scripts I have created and some downloaded from trusted sources.  Here is the link to Microsoft that talks about the policy settings.

In my case, it was set to 'Restricted'

2.  Type 'Set-ExecutionPolicy RemoteSigned' and hit enter.  This is where I got an error message stating that   access to the registry key 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsfot.PowerShell' is denied.  So when this command is executed it tries to set the execution policy for the local machine for all the users.  In my case I only need to set it for my user account not whole machine for all users.

3.  Type 'Set-ExecutionPolicy RemoteSigned -Scope CurrentUser' and hit enter.  No errors.

4.  Type 'Get-ExecutionPolicy' and hit enter and I got 'RemoteSigned' back as the configure setting.

Now I can execute powershell scripts from SQL Server Agent job to do what I need.  All of the above steps have been captured in the screen shot below.

HTH,

Bulent

VirtualBox Cannot Register The Hard Disk (UUID Already Exists)

I have been using Virtual Box to test some software on a virtual machine as opposed to running the tests on my workstation.  Everything has been working fine while I have a single virtual machine but I had to create a new virtual machine to do some more testing.

I wanted to use the existing virtual machine and not have to install it all over again.  So I copied the folder where my Virtual Machine is stored to another folder.  Then opened the VirtualBox and started to create a new virtual machine and pointed it to the new folder where I copied the contents of the folder for the first Virtual Machine and that's when I got the following error.

Then I started searching the Internet because I knew that the solution must have been available.  I came to find the solution and here is the simple fix for the problem.  We need to open a command prompt and run a command to reset the uuid for the .vdi file where we copied to initiate the second virtual machine.  Here is the steps that need to be done.
   1. Start command prompt (I recommend running it as administrator).
   2. Change your directory to VirtualBox folder.  In my case I am running x64 OS so changed my directory to "C:\Program Files\Oracle\VirtualBox\"
    3. Execute the command to reset the uuid.
        VBOXMANAGE.EXE internalcommands sethduuid <PathOfNewVDI>

The screen shot below is from my workstation which reset the uuid and then I moved on with the VirtualBox and created the new virtual machine.  So in minutes we have a new vm running to do the test.  Simple and fast as long as you know the solution.

HTH,
Bulent

SQL Server Create Identity Field Using Select Into

While working on some data issue I needed to create a backup table on the fly using SELECT INTO statement.  I knew that it was possible but never did it my self.  So it's as simple as defining a column as identity with the data type in the select statement.  Below is statement shows how it's done.

SELECT IDENTITY(INT,1,1) AS RowID,
                *
INTO       dbo.MyTable_Backup
FROM     dbo.MyTable

Hope this helps,
Bulent