Category Archives: Data

Defining a GUID type in XSD

It often happens that an element or an attribute in an XML document contains a GUID value. Because XML documents are useless without the corresponding XSD, it also often happens, that you have to define a GUID in XSD. Although XSD has support for several built-in types, unfortunately GUID is not one of them, and you have to use the classic regex solution:

<xs:schema ...>

  <xs:simpleType name="guid">
    <xs:restriction base="xs:string">
      <xs:pattern value="[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-
[0-9a-fA-F]{4}-[0-9a-fA-F]{12}" />
    </xs:restriction>
  </xs:simpleType>

After this you can refer to your new guid type just like the built-in ones:

<xs:attribute name="id" type="guid" use="required" />

Note that the regex pattern does not have $ and ^  characters in the beginning and in the end, because the pattern should always match the full value.

 

Technorati-címkék: ,

Exception calling "SqlBackup" with "1" argument(s)

We use the PowerShell script below to backup our SQL Server databases:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $dbInstance
$backup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
$backup.Action = "Database"
$backup.BackupSetDescription = "Full backup of " + $dbName
$backup.BackupSetName = $dbName + " backup"
$backup.Database = $dbName
$backup.MediaDescription = "Disk"
$backup.Devices.AddDevice("$localSqlBackupPath", "File")
$backup.SqlBackup($server)

This script runs fine for years, however recently it started to fail. It successfully backed up most databases, however the backup occasionally failed on some other databases which were previously backed up successfully. I found this error in the log:

Exception calling "SqlBackup" with "1" argument(s): 
"Backup failed for Server 'MyServer\MySqlInstance'. " At D:\Backups\BackupSite.ps1:151 char:22 + $backup.SqlBackup <<<< ($server) + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : DotNetMethodException

After a long investigation it turned out, that the error has nothing to do with how we call the SqlBackup function, instead the real issue is that it timeouts after 10 minutes. I turned off the timeout monitoring via the StatementTimeout property of the ServerConnection object and error is gone:

$server.ConnectionContext.StatementTimeout = 0

 

Technorati-címkék: ,

You may need .NET 2.0 even if you have 4.0

We have an application that is built on .NET 4.0 Client Profile and uses SQL Server Compact Edition. Because it does not rely on the latest core OS technologies, it runs very well on older Windows versions, which is required by the end-users. To test the latest version I booted a fresh Windows XP virtual machine, and successfully installed the app on it. However the app crashed at the first database operation with the following exception:

System.DllNotFoundException: Unable to load DLL ‘sqlceme35.dll’: The specified module could not be found. (Exception from HRESULT: 0x8007007E)

Obviously, the requested file was in the right folder, and we didn’t change anything in the last version of the app that could explain this exception. So what has been changed?

I couldn’t reproduce the issue neither on Windows 8.1, nor on Windows 7, and not even on older XP machines that had the previous version of the app. And although the previous version was running perfectly on XP, it crashed in the fresh VM, so my conclusion was that something is different in the VM.

First I used the VM downloaded from the modern.ie site, and I thought that VM has some specificity that breaks our app. So I installed Windows XP from ISO and spent hours and hours to install all the patches from Windows Update. However it didn’t help, the app crashed like before.

Finally I used ILSpy and peeked into the System.Data.SqlServerCe assembly, and because it was referencing .NET Framework 2.0, I gave it a try and installed that older Framework version side-by-side to the new version. And to my surprise the issue was gone!

The beauty of the case:

  • Windows Update was not installing .NET Framework 2.0, only the newer version. It was not the case previously.
  • The .NET 4.0 Client Profile was not enough, SQL Compact Edition required version 2.0 as well.
  • I understand that there was no problem on Windows 7, because that OS contains .NET 2.0, but why didn’t the app crash on Windows 8.1 which doesn’t install .NET 2.0 by default?

 

Technorati-címkék:

BIDS is dead. Long live SSDT BI.

The SQL Server team announced yesterday, that a new Visual Studio 2012 extension which contains templates for Reporting, Analysis and Integration Services projects is released online.

ssdt-bi

The set of tools which was known as Business Intelligence Development Studio earlier is now named SQL Server Data Tools – Business Intelligence (SSDT BI), and works seamlessly with projects created in VS 2010 without upgrade/downgrade, so you don’t have to install the earlier version just for this.

Announcement: http://blogs.msdn.com/b/sqlrsteamblog/archive/2013/03/06/sql-server-data-tools-business-intelligence-for-visual-studio-2012-released-online.aspx

Download (782 MB): http://www.microsoft.com/en-us/download/details.aspx?id=36843

 

Technorati-címkék: ,

Installing .NET Framework 3.5 in Windows 8 and in Windows Server 2012

I was installing SQL Server 2012 on Windows Server 2012, and everything seemed perfect. Although checking the prerequisites completed successfully, the installer displayed a warning popup in the middle of the setup about the required but missing .NET Framework 3.5. Because the dialog had only an OK button, I could only hope that .NET 4.5 which comes with the OS will be suitable for SQL Server. It wasn’t, and the installation failed. And it turned out, that installing .NET Framework 3.5 is not so easy in these new operating systems.

In Windows 8 and in Windows Server 2012, the .NET Framework 3.5 is a so-called Feature on Demand. This means, that the metadata for the feature is included in Windows 8 and in Windows Server 2012, but the binaries and other files associated with the feature are not included. And if you want to install the feature, you have to supply the files.

 

Windows 8

In Windows 8 open the Programs and Features dialog. For me the easiest way to access this dialog is via the Windows+X admin menu:

net35-windows8-1

Select .NET Framework 3.5 (includes .NET 2.0 and 3.0) then click OK. A little searching…

net35-windows8-2

…and a question, whether you really want to download the files from Windows Update:

net35-windows8-3

Maybe it’s just me, but I really miss the info about the size of the download, and the option to supply the files locally. So if you really want .NET 3.5, click Download files from Windows Update. A little downloading…

net35-windows8-4

… then if you are not so lucky (like me), this error screen:

net35-windows8-5

The Tell me how to solve this problem link is exceptionally useful, because it links to the KB2734782 Knowledge Base article (Error codes when you try to install the .NET Framework 3.5 in Windows 8 or in Windows Server 2012) which can really help. In my case the problem was that my computer is a domain member, and group policy centrally configures the Windows Update settings. As I couldn’t change the policy, I had no other option than installing the .NET Framework from command line. Luckily the installation files are on the Windows 8 installation media, and with this short command you can install it from there:

dism /online /enable-feature /featurename:NetFx3 /All /Source:D:\sources\sxs /LimitAccess

It completes in a few seconds, I guess it’s much faster, than from Windows Update:

net35-windows8-6

 

Windows Server 2012

The command line installation using dism works perfectly on Windows Server 2012 too, because the installation files are available also on the server installation media. Ironically, on the server, you can complete the installation on the GUI as well. Just start the Add Roles and Features Wizard and select the .NET Framework 3.5 Features option (click for larger image):

net35-ws2012-1

After the Next, just try to read the endless warning message:

net35-ws2012-2

The message tries to warn you to click the Specify an alternate source path link at the bottom:

net35-ws2012-3

Then enter the path of the source files, like D:\Sources\SxS:

net35-ws2012-4

That’s it. After you finish the wizard, SQL Server 2012 installs without any issue.

 

Publishing SQL Server on a non-default port

If you afraid of malicious users and malwares that target port 1433, you can publish your SQL Server on a different port.

First, start SQL Server Configuration Manager, then navigate to the SQL Server Network Configuration –> Protocols for [instance neve]> TCP/IP branch, and open the Properties dialog (click for the full image):

sql-port-configuration-manager

On the second, IP Addresses tab you can configure the IP addresses and ports your database server instance is listening, and for example you can move it to port 8765:

sql-port-tcp-properties

Obviously, you have to open this port on the firewall for inbound connection. If you don’t want to hardcode the port number into the firewall rules, you can create a firewall rule for the SQL process itself. Start Windows Firewall with Advanced Security then create a new Inbound Rule by clicking New Rule… on the right pane. Here’s the trick: create a rule for a Program, and not for a port:

sql-port-firewall-1

Then specify the full path of the sqlservr.exe of your SQL Server instance:

sql-port-firewall-2

There’s nothing new on next steps, just create the rule as usual.

But, if your server is listening on a non-default port, how will your clients know which port should they connect to? That’s the purpose of the SQL Browser Service, that can tell the clients the dynamic port numbers of the instances. However, if you publish your SQL  Browser Service, anyone could query the port numbers, so moving the instances to other ports wouldn’t make any sense. The only solution is to explicitly specify the port number during the connection. Note that the syntax is a bit special, you should use a comma, instead of the usual colon:

sql-port-connection

Specifying the port number seems inconvenient, however you have to do it only for remote connections. Behind the firewall you can freely run your SQL Browser Service, so applications (websites) running on the server will be able to find the right SQL instance without hardcoding the port number into their connection strings.

 

Technorati-címkék: ,

How to encrypt SQL TDS connections

If your SQL clients (for example developers’ SQL Management Studios) and SQL Servers are not really “close” to each other, you should encrypt the tabular data stream (TDS) channel with SSL.

First, you will need a valid certificate. It must be issued by a trusted third party and the FQDN on it must exactly match the name of the SQL Server. To make your life easier, don’t trick with DNS aliases and DNS suffixes.

Next, import the certificate to the Personal Certificates store of the Local Computer account. Start the Microsoft Management Console (mmc), add the Certificates snap-in and when asked, select the Computer account. Expand the Personal\Certificates branch, then you can find the Import… menu item in the All Tasks group of the context menu:

sql-ssl-import

To make sure your SQL Server can use this certificate, select the certificate on the right pane, then click the All Tasks –> Manage Private Keys… menu item:

sql-ssl-acl

You will get a usual ACL editor dialog. Click Add and select the account that runs your SQL Server instance. If you use SQL Server 2012, it by default runs with a managed service account that you can reference as NT Service\MSSQL$instancename. After selecting the account grant Read permission to it (you don’t need Full control!):

sql-ssl-ace

Start SQL Server Configuration Managert and navigate to the SQL Server Network Configuration –> Protocols for [instance name] branch, then open the Properties dialog:

sql-ssl-properties

Click the second, Certificate tab and select the certificate from the list:

sql-ssl-properties-certificate

If you don’t see your certificate here, check the certificate’s validity, FQDN, name resolution and permissions.

On the first, Flags tab, you can enable Force Encryption to make sure that all clients encrypt the communication:

sql-ssl-properties-force-encryption

By forcing the encryption you enable only those clients, that specify Encrypt=True in the connection string. If the client is SQL Server Management Studio, you can enable encryption on the Connection Properties tab by checking the Encrypt connection checkbox:

sql-ssl-ssms-encrypt

You can verify the encryption by Wireshark, or you can query the properties of the active connections directly from SQL Server via the sys.dm_exec_connections dynamic management view. This query lists the most important connection properties:

SELECT session_id, net_transport, client_net_address, local_net_address, 
       local_tcp_port, auth_scheme, encrypt_option 
FROM sys.dm_exec_connections

You can sleep well, if you see TRUE in the encrypt_option column.

 

Technorati-címkék: ,,,