Configuring SQL Server xp_cmdshell

Newer versions of SQL, both the Full and Express, do not have the “sqlcmd Utility” installed by default. The GeoCue database update process utilizes this feature in order to apply updates to the database. Hence, it needs to be installed and enabled before a database update can be run successfully. A database administrator may disable xp_cmdshell once the database has been successfully updated, however, it must be enabled again prior to installing a new version of GeoCue or applying a service pack to the GeoCue Server. Failure to enable this feature results in an error message reporting that, “SQL Server blocked access to procedure ‘sys.xp_cmdshell’ …”

There are two ways to enable xp_cmdshell, but it must be installed first. Install “sqlcmd Utility” from https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver15:

Probable Resolution #1:

OUses SQL queries and is outlined in the “Configure xp_cmdshell” section of the GeoCue 2020 Installation Guide:

1. Open up Query Browser or MS SQL Server Management Studio and log in as SA or another privileged user. Open a new query.

Run sp_configure

2. Then scroll all the way to the bottom, if xp_cmdshell is there, then skip these 2 steps, otherwise run

sp_configure show_advanced_options, 1

reconfigure with override

3. Now run the following commands exec

sp_configure xp_cmdshell, 1

reconfigure with override

4. Now you can use xp_cmdshell in all your scripts

Probable Resolution #2

Uses the GUI and is simpler to use if you’re not familiar with running scripts in SQL, and are running SQL Server 2008 R2, or later, with SQL Server Management Studio. If that is applicable to your scenario, then you may follow these steps instead:

1. Open SQL Server Management Studio
2. Connect to the SQL Instance hosting your GeoCue database.
3. Right-click on your SQL Instance, listed at the top on the left hand side under Object Explorer, and select Facets.
4. From the Facet dropdown at the top of the dialog select Surface Area Configuration.
5. Set XPCmdShellEnabled to True.
6. Then ok to close the Facets dialogue.