Create and connect to a SQL Database on Windows Azure

Azure, is one of the fastest growing cloud platform by Microsoft. In this simple post, I will create and connect to a SQL Database on Microsoft Azure.

To get started logon to the Microsoft Azure Management Portal , navitage to SQL Database and click on New.

sqldatabases_new

In the next screen, give the database a unique name and select the appropriate values.

CreateNewDatabase

The database gets created in a few seconds and it would be ready to use when the status changes to ONLINE.

DatabaseOnline

Clicking on the Database Name (in this case is is TheTestDatabase), would give the detailed information about the database including the address of the database.DatabaseProperties

Now you can use this information in the Connection String or just connect to it using SQL Server Management Studio (SSMS). But when you try to connect to this database from a different computer, it would fail with the following error.firewallerror

Error text:


Cannot open server 'ServerName' requested by the login. Client with IP address '{IPAddress}' is not allowed to access the server.
To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range.
It may take up to five minutes for this change to take effect.
Login failed for user 'pradeep'.
This session has been assigned a tracing ID of '{TrackingID}'.
Provide this tracing ID to customer support when you need assistance. (Microsoft SQL Server, Error: 40615)

As seen in the error message, by default all IP addresses are not allowed to connect to a SQL Database. The requisite IP Addresses/IP Address ranges needs to explicitly configured for the server hosting the SQL Database, else the connections will fail with the above error.

To configure the Firewall rules, first we need to navigate to the server on which the SQL database is hosted.

ServerName

In the Configure screen, give the rule a name and specify the IP address range which is allowed to connect to the database.

FirewallRule

After entering the details make sure to click on the “Save” button at the bottom of the screen. Now if we try connecting to the database from the same host, the connection would succeed and the database would be listed in SSMS.

DatabaseConnected

Hope you found this post useful. Happy cloud computing!

Log reader fails with “The process could not execute ‘sp_replcmds’ ” error

Recently I worked on an issue in which the Replication wasn’t working right after setting it up. The Publication and the subscriptions were created successfully but the subscription was still uninitialized.

As a first step checked the status of the Snapshot Agent under Replication Monitor. It was failing with the following error message.

The concurrent snapshot for publication 'Sitecore_301_Redirect' is not available because it has not been fully generated or the Log Reader Agent is not running to activate it.

The Snapshot agent was correct that the snapshot was not generated and also the Log Reader agent wasn’t running. The Log Reader agent was failing with the error below.

The process could not execute 'sp_replcmds' on {ServerName}

The above message a very generic in nature. Hence I added a verbose log to the Log Reader Agent as explained in this KB article.

-Continuous -Output "D:\VerboseLog\Log.txt" -Outputverboselevel 3
LogReader_VerboseLog

 

Here is what was recorded in the Log Reader agent when the job failed the next time

Status: 0, code: 15517, text: 'Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.'.

sys.databases DMV reported that the current owner of the job wasn’t a DBO on the Publication Database. Hence the next logical step was to make the job owner the DBO of the database by executing

sp_changedbowner '{LoginName}'

After this change, all the replication agents worked without any problems and data started flowing to the subscriber.

SQL Server Agent failing to start with the error “StartServiceCtrlDispatcher failed (error 6)”

Recently I worked on an issue where SQL Server Agent was failing to start. Before this there was some problems with the disks on the server and the databases were restored from backups.

I started looking for errors in few of the logs

  • System Event Log – No errors
  • SQL Server Error Log- No errors

Looked for the SQL Server Agent logs in the directory where SQL Server Error Logs are located but could not find any. The attempts to start the service from services.msc and SQL Server Configuration Manager failed with a generic error message.

Hence I took the SQL Server Agent service’s binary path from the service properties.

BinaryPath

Then typed “…..Binn\SQLAGENT.EXE” -i MSSQLSERVER” the same command in the command prompt.

ErrorMessage_StartServiceCtrlDispatcher

Error message “StartServiceCtrlDispatcher failed (error 6).” doesn’t help! Hence I started sqlserveragent.exe with the “-c” parameter which indicates SQL Server Agent is running in console mode.

“…..Binn\SQLAGENT.EXE” -i MSSQLSERVR -c”

Now the details came out!

CannotFindPath

SQL Server Agent is trying to rename D:\Data3\SQLAGENT.OUT to D:\Data3\SQLAGENT.1
It is failing to start since the file doesn’t exist because the drive isn’t there!

Now time to change the SQL Server Agent Log path. Tried executing the following command but it failed

EXEC msdb.dbo.sp_set_sqlagent_properties @errorlog_file=N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT’

Msg 15281, Level 16, State 1, Procedure sp_set_sqlagent_properties, Line 0
SQL Server blocked access to procedure ‘dbo.sp_set_sqlagent_properties’ of component ‘Agent XPs’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Agent XPs’ by using sp_configure. For more information about enabling ‘Agent XPs’, see “Surface Area Configuration” in SQL Server Books Online.

The only option left was to modify the SQL Server Agent Error Log path in the Registry.
Navigated to the following registry key and modified it to point to the correct path.

RegistryPath

As expected now the SQL Server Agent started successfully.

Add node fails with Attempted to read or write protected memory error

Recently I had worked on an issue where the end user was trying to add a third Node to an existing 2 node cluster. Soon after the setup is intiated, it would fail with the following error message (snippet from the Summary.txt).

Exception type: System.AccessViolationException
    Message: 
        Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
    Data: 
      DisableWatson = true
    Stack: 
        at Microsoft.SqlServer.Chainer.Infrastructure.MsiNativeMethods.MsiOpenPackageEx(String szPackagePath, UInt32 dwOptions, UInt32& hProduct)
        at Microsoft.SqlServer.Configuration.MsiExtension.InstallPackage.SetCommonProperties()
        at Microsoft.SqlServer.Configuration.MsiExtension.InstallPackage.RunMsiCore(String commandLine)
        at Microsoft.SqlServer.Configuration.MsiExtension.InstallPackage.RunMsiWithRetry(String commandline)
        at Microsoft.SqlServer.Configuration.MsiExtension.InstallPackage.RunMsi(IEnumerable`1 commandLineProps)
        at Microsoft.SqlServer.Configuration.SetupExtension.MSIInstallerEngine.InstallPackage(PackageId pkg, InstallAction pkgAction)
        at Microsoft.SqlServer.Configuration.MsiExtension.PackageInstallAction.Execute(String actionId, TextWriter errorStream)
        at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(TextWriter statusStream, ISequencedAction actionToRun)

“Attempted to read or write protected memory. This is often an indication that other memory is corrupt.” Scary little error message! Since this is a setup related issue, the best place to start troubleshooting it is from the Setup Logs.  This article has more details on how to read SQL Server Setup logs.

Detail.txt pointed in the right direction as seen below.

Opening existing patch 'c:WindowsInstaller97363c.msp'.
Couldn't find local patch 'c:WindowsInstaller97363c.msp'. Looking for it at its source.
Resolving Patch source.
SOURCEMGMT: Looking for sourcelist for product {D7C6A337-F6BB-46CB-AE32-204DD6A8825D}
SOURCEMGMT: Trying source c:7cb5fee2d4775f9d53c7f95659x64setup.
Note: 1: 2203 2: c:7cb5fee2d4775f9d53c7f95659x64setupsql_ssms.msp 3: -2147287037 
SOURCEMGMT: Source is invalid due to missing/inaccessible package.
Unable to create a temp copy of patch 'sql_ssms.msp'.
Searching provided command line patches for patch code {D7C6A337-F6BB-46CB-AE32-204DD6A8825D}
Could not find source for missing patch {D7C6A337-F6BB-46CB-AE32-204DD6A8825D} -- orphaning this patch
SequencePatches starts. Product code: {72AB7E6F-BC24-481E-8C45-1AB

Now it was very clear that the issue is the classic missing MSI/MSP file issue. It was just matter of copying the right files to C:WindowsInstaller as explained here and here.

Once the missing files were replaced, the setup completed without any further errors.

Jobs fail with “Cannot execute as the database principal because the principal “dbo” does not exist this type of principal cannot be impersonated or you do not have permission” error

Recently I worked on an issue in which the jobs which were running fine started failing all of the sudden. The job history had the following error message.

Cannot execute as the database principal because the principal "dbo" does not exist this type of principal cannot be impersonated or you do not have permission

But the principal “dbo” exists, it can be impersonated and I have permission because I am the sysadmin! Now what changed?

Checked the job properties and “sa” was the job owner. This job was last modified a few years ago. The next step is to find out the properties of the database against which the job is executing the queries.

When I right clicked on the database and selected properties, got the following error.

Property Owner is not available for Database ‘[DatabaseName]‘. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (Microsoft.SqlServer.Smo)

This error is generated when the database owner’s login does not exist in the active directory and I had blogged about it here.

When I executed sp_helpdb, the output looked like the one below.

sp_helpdb_invalidowner

As evident from the output, the database owner’s login was not present in the Active Directory and it was reported as ~~unknown~~. Now that we know the problem, the resolution is pretty simple. Just executed sp_changedbowner or ALTER AUTHORIZATION command against the databases in question. The job started running successfully.

The next time you create a database please take care to change the database owner to a different login. Else you leave the organization and someone else needs to cleanup your name from all the databases that you had created!

Database mirroring cannot be enabled because the database is not in full recovery mode on both partners

Recently I had worked on a mirroring issue. While initializing mirroring, an error message as shown in the below screenshot is encountered.

mirror2

Here’s how this issue was fixed

  • Check if the recovery model of the database on the Prinicipal Server was set to FULL. <– Yes, it was.
  • Take the full database backup of the database on the Prinicpal server
  • Take the transaction log backup of the database on the Principal Server
  • Restore the full database backup on the mirrored instance WITH NORECOVERY
  • Restore the transaction log backup on the mirrored instance WITH NORECOVERY

Configure Mirroring again.

SQL Server Install | Use Role Management Tool to install Microsoft .Net framework 3.5 SP1

I was trying to setup SQL Server 2008 on my lab machine running on Windows Server 2008 R2. When the setup was installing the prerequisites, the following error message popped up.

.net2

This is a very simple error message, which a DBA would come across often. However the next screen which popped up after this message was interesting.

.net1

Since Windows Server 2008 R2 has the .Net framework 3.5 already available as a feature, all we need to do is just enable that feature. There is no need to download the installer and install it separately. As the error message states, just enable it using the “Role Management Tool”. Role Management tool can be found under Server Manager –> Features –> Add features as seen in the following screenshot.

.net3

.net4

That’s all. Now the .Net framework 3.5.1 is installed and ready to use! After this the SQL Server setup completed successfully.