Tips & Tricks – Database Math

MSSQL Server Connectivity: UDL Test

The “UDL Test” is a quick, easy, and flexible way to test connectivity to Microsoft SQL Server using a variety of installed providers/drivers using either Windows or SQL Authentication.

To get started, simply create a new text file on your desktop and give it a name:

Change the file extension to udl:

You will get this warning:

Click Yes.

The icon should change:

Now just double click to launch:

By default the Microsoft OLEDB Provider for SQL Server is specified but you can change the provider you want to use under the Provider tab:

Under the Connection tab, specify the server name and credentials you want to use to connect to either a local or remote SQL Server instance:

NOTE:  If you select "Windows NT Integrated security" the User name and password fields are greyed out. This is because the connection will use a token from the Windows account you are currently signed in with.

Lastly, click Test Connection and you should see either a “Test connection succeeded.” or “Test connection failed” pop-up:

NOTE: If you click the drop down for "Select the database on the server" this instantly tries to form a connection to master in order to return a list of available databases.

MSSQL SERVER: Backup and Restore to Network Path Using XP_CMDSHELL

There might be times that you need to backup or restore databases in MSSQL Server to a network location. Out of the box, this isn’t the easiest thing to do. So I will show you the steps you can take to successfully backup and restore databases to and from network mapped drives.

Start by mounting a network drive (optional):

Enter the path for the network drive and click Next:

You will be prompted to enter credentials, do so then Finish

You’ll now see a new drive under Network Locations with your given drive letter:

However, if you go to SSMS and try to backup a database via the GUI you won’t be able to see the drive letter:

If you try to do it via script instead, you’ll get an operating system error:

Msg 3201, Level 16, State 1, Line 1

Cannot open backup device ‘Z:\test.bak’. Operating system error 3(The system cannot find the path specified.).

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

This is because SQL Server is registered and running as a service running under the context of the SQL Server service account. The service runs in the environment of the local console with the security of this service account. Network mapped drives are specific to a session and not visible to services started on the local console.

So how do we work around this? By using XP_CMDSHELL.

First, make sure XP_CMDSHELL is enabled:

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE
GO

Next, use the following command, replacing Z: \\mymachine\localvmshare with your own network path:

EXEC XP_CMDSHELL 'net use Z: \\mymachine\localvmshare'

The drive should now be mapped. You can test that it is properly mapped with the following:

EXEC XP_CMDSHELL 'Dir Z: '

If you mistype the path you might see an error like these:

System error 53 has occurred. The network path was not found.

If you get this error just correct the typo and run the command again.

NOTE: To remove the mapping once you are done with it, or to remove a mapping that was mistyped, you can run the following command: EXEC XP_CMDSHELL 'net use Z: /delete'

Another error you could possibly get is this one:

The operation was canceled by the user.

If this occurs, modify the XP_CMDSHELL statement like so, where serviceaccount is a user that has permission to mount the network drive and passwordgoeshere is the password for the user account:

EXEC XP_CMDSHELL 'net use Z: \\mymachine\localvmshare /user:serviceaccount passwordgoeshere'

Now, in the GUI, we should be able to see our newly mapped drive in the backup/restore wizard:

If we rerun our script to backup, it is now successful:

And there is our backup file:

NOTE: The next time the SQL Server Service is restarted, the mapping will be removed and if you have automated backup jobs configured to use these drives, they will fail. If you decide to rely on this method for network drive backups, you will need to add logic to your backup jobs to check that the drive mapping exists first or you will need to have a startup proc configured to run and map the drive on restart.

In the case of the client I was working with, we needed to setup database mirroring via backup and restore but the local machine did not have enough disk space free to accommodate the backups. However, it is work noting that XP_CMDSHELL is a powerful feature that runs under the context of the SQL Server service account. Malicious users sometimes attempt to elevate their privileges by using XP_CMDSHELL so you should keep this in mind when deciding if you should use XP_CMDSHELL in your maintenance/backup strategy on a permanent basis.

For more information on XP_CMDSHELL, see here.

MSSQL SERVER: Create an Extended Event Session with a “NOT LIKE” filter criteria on sql_statement_completed

As of the writing of this post, when creating extended events sessions in SQL Server Management Studio (SSMS), on the Set Session Event Filters page of the New Session Wizard, there is an option to include events that are like a specified value (using the like_i_sql_unicode_string operator) but there is not currently anything in the GUI to EXCLUDE queries that meet this filter criteria.

Adding a “NOT LIKE” clause to the extended events filter seems to be a limitation of the GUI currently. The steps below show you how to select all of your extended events via the wizard and then add the “NOT LIKE” functionality at the end by scripting out the Extended Event and making a minor tweak.

Open the New Session Wizard in SSMS:

Give it a name

Select “Do not use a template” and click Next

Search for sql_statement_completed in the list and add it to the Selected Events and click Next

On the Capture Global Fields page, click Next

On the Set Session Event Filters page, select “sqlserver.sql_text from the “Field” drop down list and set the “Operator” to like_i_sql_unicode_string and set the “value” field to the full or partial sql query that you would like to exclude. Then click next.

Continue to the Summary page of the wizard and click on the “Script” button

You should see a script like this:

We’re almost there but this script in its current form will do the opposite of what we want. This script will capture ONLY the sp_BlitzFirst execution. To have it exclude these executions, we have to make the following change:

Here is the TSQL text from this proof of concept:

CREATE EVENT SESSION [XEventFilterTest] ON SERVER

ADD EVENT sqlserver.sql_statement_completed(

    WHERE ( NOT [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'EXEC sp_BlitzFirst%')))

WITH (STARTUP_STATE=OFF)

GO

Other Notes:

The total number of characters allowed for ALL filters on an event is 3,000. If you execeed this threshold, you may see an error that resembles the following:

Msg 25716, Level 16, State 1, Line 1
The predicate on event, "sqlserver.attention", exceeds the maximum length of 3000 characters.

To workaround this issue, split your Extended Events Session into multiple sessions.

MSSQL SERVER: ERROR: CREATE TABLE permission denied in database ‘tempdb’

I was recently working with a client that was periodically but inconsistently getting a strange error when one of their SQL Server Agent jobs ran:

ERROR: CREATE TABLE permission denied in database 'tempdb'

Why is this error confusing? Well, at a minimum every database and every user has at least the public role. The public role is contained in every database, which includes system databases. It cannot be dropped and you cannot add or remove users from it. Permissions granted to the public role are inherited by all other users and roles because they belong to the public role by default.

We also know that any user with the public role can create temporary tables in tempdb. So, how on earth does this error make any sense?

Let me demonstrate.

It is still true that with only the public role a user can create both local and global temporary tables.

If you want to demonstrate this, create a new user in SQL and make sure that it only has the public role then try to create some tables like below:

Local temp table:

CREATE TABLE #test_table_local (
     product_name VARCHAR(MAX),
     list_price DEC(10,2)
 );

Global temp table:

CREATE TABLE ##test_table_global (
     product_name VARCHAR(MAX),
     list_price DEC(10,2)
 );

But watch what happens we we try to create a regular “permanent” table in tempdb:

CREATE TABLE test_table_permanent (
     product_name VARCHAR(MAX),
     list_price DEC(10,2)
 );

So, as you can see, the original statement is still true. The public role allows users to create temp tables in tempdb without any additional permissions. However, to create permanent tables in tempdb you will still need the CREATE TABLE permission.

In my client’s case it was a matter of a simple typo. One particular code path in the SQL Agent job triggered a code path containing the typo where a CREATE TABLE statement was missing a ‘#’ in front of the table name.

If you’re wondering why you would create a “permanent” table in tempdb, you wouldn’t in most cases but it does have some unique properties in that the table is available from other sessions (unlike local temporary tables), and it is not deleted automatically even if there are no connections to it (unlike global temporary tables). However, it does still get deleted when the instance is restarted (just like everything else in tempdb).

Reference Links & More Information

Server and Database Roles in SQL Server | The Public Role

MSSQL Server: Opening the Required Firewall Ports

Firewalls are obviously a very important and fundamental part of network security. However, that doesn’t make them any less annoying when you’re working in development or just trying to learn the nitty gritty. You may be tempted to just disable the firewall. This will work and you’ll be on your merry way but you’ve learned nothing and when its time to deploy it can be a headache.

When it comes to Microsoft SQL Server, the default instance is easy enough. It operates on port 1433 out-of-the-box. Lets look at how to open this port a couple of different ways.

Using the GUI

To open it up for remote access, simply open Windows Defender Firewall -> Advanced Settings -> Inbound Rules -> New Rule… then follow the wizard:

Select Port and click Next.

Select TCP and type 1433 into the Specify local ports field

New Inbound Rule Wizard: Protocol and Ports

Select Allow the connection and click Next.

Select the Network Profiles that you want the rules to apply to.

Give the rule a name and description the click Finish.

Using netsh advfirewall

Using the GUI can be time consuming, if you have multiple machines were you need to open ports we can use the netsh utility to administer the firewall through the command line or a batch file.

Running this command will create an inbound rule that opens communication on port 1433 for the domain network profile:

netsh advfirewall firewall add rule name = SQLPort dir = in protocol = tcp action = allow localport = 1433 remoteip = localsubnet profile = DOMAIN

By default, the typical ports used by SQL Server and associated database engine services are: TCP 1433, 4022, 135, 1434, UDP 1434. The table below explains these ports in greater detail. A named instance uses dynamic ports.

The following table lists the ports that are frequently used by the Database Engine.

Scenario	Port	Comments
Default instance running over TCP	TCP port 1433	This is the most common port allowed through the firewall. It applies to routine connections to the default installation of the Database Engine, or a named instance that is the only instance running on the computer. (Named instances have special considerations. See Dynamic Ports later in this article.)
Named instances with default port	The TCP port is a dynamic port determined at the time the Database Engine starts.	See the discussion below in the section Dynamic Ports. UDP port 1434 might be required for the SQL Server Browser Service when you are using named instances.
Named instances with fixed port	The port number configured by the administrator.	See the discussion below in the section Dynamic Ports.
Dedicated Admin Connection	TCP port 1434 for the default instance. Other ports are used for named instances. Check the error log for the port number.	By default, remote connections to the Dedicated Administrator Connection (DAC) are not enabled. To enable remote DAC, use the Surface Area Configuration facet. For more information, see Surface Area Configuration.
SQL Server Browser service	UDP port 1434	The SQL Server Browser service listens for incoming connections to a named instance and provides the client the TCP port number that corresponds to that named instance. Normally the SQL Server Browser service is started whenever named instances of the Database Engine are used. The SQL Server Browser service does not have to be started if the client is configured to connect to the specific port of the named instance.
Instance with HTTP endpoint.	Can be specified when an HTTP endpoint is created. The default is TCP port 80 for CLEAR_PORT traffic and 443 for SSL_PORT traffic.	Used for an HTTP connection through a URL.
Default instance with HTTPS endpoint	TCP port 443	Used for an HTTPS connection through a URL. HTTPS is an HTTP connection that uses Transport Layer Security (TLS), previously known as Secure Sockets Layer (SSL).
Service Broker	TCP port 4022.	There is no default port for SQL ServerService Broker, but this is the conventional configuration used in Books Online examples.
Database Mirroring	Administrator chosen port.	There is no default port for database mirroring however Books Online examples use TCP port 5022 or 7022. It is important to avoid interrupting an in-use mirroring endpoint, especially in high-safety mode with automatic failover. Your firewall configuration must avoid breaking quorum. For more information, see Specify a Server Network Address (Database Mirroring).
Replication	Replication connections to SQL Server use the typical regular Database Engine ports (TCP port 1433 for the default instance, etc.) Web synchronization and FTP/UNC access for replication snapshot require additional ports to be opened on the firewall. To transfer initial data and schema from one location to another, replication can use FTP (TCP port 21), or sync over HTTP (TCP port 80) or File Sharing. File sharing uses UDP port 137 and 138, and TCP port 139 if it using NetBIOS. File Sharing uses TCP port 445.	For sync over HTTP, replication uses the IIS endpoint (ports for which are configurable but is port 80 by default), but the IIS process connects to the backend SQL Server through the standard ports (1433 for the default instance. During Web synchronization using FTP, the FTP transfer is between IIS and the SQL Server publisher, not between subscriber and IIS.
Transact-SQL debugger	TCP port 135 See Special Considerations for Port 135 The IPsec exception might also be required.	If using Visual Studio, on the Visual Studio host computer, you must also add Devenv.exe to the Exceptions list and open TCP port 135. If using Management Studio, on the Management Studio host computer, you must also add ssms.exe to the Exceptions list and open TCP port 135.

Service Broker and AlwaysOnAG (mirroring) don’t have defaults. If you need to check what these ports are configured as on an existing system, connect via SSMS or SQLCMD and run the following queries:

AlwaysOn AG (Mirroring):

SELECT name, protocol_desc, port, state_desc 
   FROM sys.tcp_endpoints
   WHERE type_desc = 'DATABASE_MIRRORING'

Service Broker:

SELECT name, protocol_desc, port, state_desc
   FROM sys.tcp_endpoints
   WHERE type_desc = 'SERVICE_BROKER'

Batch Script

Below is an example batch script that creates all of the aforementioned firewall rules. Modify the ag_endpnt and sb_endpnt variable at the beginning if your ports are different. Script should be executed from an elevated command prompt or powershell window. Provided for educational purposes only and as always don’t run anything in production that you haven’t tested first.

rem OpenSQLServerPorts.bat
rem Date Modified: 03/26/2021
rem Author: Avery Lane

set "ag_endpnt=5022"
set "sb_endpnt=4022"

rem SQL Default Ports Inbound/Outbound
netsh advfirewall firewall add rule name = SQLPort dir = in protocol = tcp action = allow localport = 1433 remoteip = localsubnet profile = DOMAIN
netsh advfirewall firewall add rule name = SQLPort dir = out protocol = tcp action = allow localport = 1433 remoteip = localsubnet profile = DOMAIN

rem SQL Dedicated Admin Port Inbound (Default Instance)
netsh advfirewall firewall add rule name = SQLPortDedicatedAdmin dir = in protocol = tcp action = allow localport = 1434 remoteip = localsubnet profile = DOMAIN

rem SQL Database mirroring/AlwaysOn AG (Default is 5022)
netsh advfirewall firewall add rule name = SQLPortAG dir = in protocol = tcp action = allow localport ="%ag_endpnt%" remoteip = localsubnet profile = DOMAIN
netsh advfirewall firewall add rule name = SQLPortAG dir = out protocol = tcp action = allow localport ="%ag_endpnt%" remoteip = localsubnet profile = DOMAIN

rem SQL Browser Service
netsh advfirewall firewall add rule name = SQLPortBrowser dir = in protocol = udp action = allow localport = 1434 remoteip = localsubnet profile = DOMAIN

rem Service Broker (Default is 4022)
netsh advfirewall firewall add rule name = SQLPortServiceBroker dir = in protocol = tcp action = allow localport ="%sb_endpnt%" remoteip = localsubnet profile = DOMAIN
netsh advfirewall firewall add rule name = SQLPortServiceBroker dir = out protocol = tcp action = allow localport = "%sb_endpnt%" remoteip = localsubnet profile = DOMAIN

rem Replication
netsh advfirewall firewall add rule name = SQLReplUDP dir = in protocol = udp action = allow localport = 137,138 remoteip = localsubnet profile = DOMAIN
netsh advfirewall firewall add rule name = SQLReplTCP dir = in protocol = tcp action = allow localport = 139,445 remoteip = localsubnet profile = DOMAIN

netsh advfirewall firewall add rule name = SQLReplUDP dir = out protocol = udp action = allow localport = 137,138 remoteip = localsubnet profile = DOMAIN
netsh advfirewall firewall add rule name = SQLReplTCP dir = out protocol = tcp action = allow localport = 139,445 remoteip = localsubnet profile = DOMAIN

Output of netsh advfirewall firewall add rule ?

Usage:
      add rule name=<string>
      dir=in|out
      action=allow|block|bypass
      [program=<program path>]
      [service=<service short name>|any]
      [description=<string>]
      [enable=yes|no (default=yes)]
      [profile=public|private|domain|any[,...]]
      [localip=any|<IPv4 address>|<IPv6 address>|<subnet>|<range>|<list>]
      [remoteip=any|localsubnet|dns|dhcp|wins|defaultgateway|
         <IPv4 address>|<IPv6 address>|<subnet>|<range>|<list>]
      [localport=0-65535|<port range>[,...]|RPC|RPC-EPMap|IPHTTPS|any (default=any)]
      [remoteport=0-65535|<port range>[,...]|any (default=any)]
      [protocol=0-255|icmpv4|icmpv6|icmpv4:type,code|icmpv6:type,code|
         tcp|udp|any (default=any)]
      [interfacetype=wireless|lan|ras|any]
      [rmtcomputergrp=<SDDL string>]
      [rmtusrgrp=<SDDL string>]
      [edge=yes|deferapp|deferuser|no (default=no)]
      [security=authenticate|authenc|authdynenc|authnoencap|notrequired
         (default=notrequired)]

Remarks:

      - Add a new inbound or outbound rule to the firewall policy.
      - Rule name should be unique and cannot be "all".
      - If a remote computer or user group is specified, security must be
        authenticate, authenc, authdynenc, or authnoencap.
      - Setting security to authdynenc allows systems to dynamically
        negotiate the use of encryption for traffic that matches
        a given Windows Defender Firewall rule. Encryption is negotiated based on
        existing connection security rule properties. This option
        enables the ability of a machine to accept the first TCP
        or UDP packet of an inbound IPsec connection as long as
        it is secured, but not encrypted, using IPsec.
        Once the first packet is processed, the server will
        re-negotiate the connection and upgrade it so that
        all subsequent communications are fully encrypted.
      - If action=bypass, the remote computer group must be specified when dir=in.
      - If service=any, the rule applies only to services.
      - ICMP type or code can be "any".
      - Edge can only be specified for inbound rules.
      - AuthEnc and authnoencap cannot be used together.
      - Authdynenc is valid only when dir=in.
      - When authnoencap is set, the security=authenticate option becomes an
        optional parameter.