Microsoft SQL Server

Overview

Signals reads conversion and customer data straight from a SQL Server database on a schedule and sends the columns you nominate to your ad platforms.

Reading it directly means there is no file job to maintain and nothing to go stale between runs. Signals reads only; nothing is written back.

You can point it at a table, or at a query. The query path is what you want when the shape Signals needs does not match anything you already have.

Azure SQL Database speaks the same client protocol and is not the same thing to configure. It has no named instances and no Browser service to locate one, and it refuses USE to move an open connection to another database, so a second database means a second connection. The endpoint answers on 1433, which is the whole of what a connection from outside Azure needs under the default Proxy connection policy; under the Redirect policy the client also needs outbound TCP in the 11000 to 11999 range. Where this page describes a step taken at the server, Azure needs that step run in a session of its own against master.

Supported destinations

Destinations supported by Microsoft SQL Server

Category	Supported
Offline Conversions	Google Offline Conversions API Google Store Sales LinkedIn Offline Conversions API Meta Offline Conversions API OpenAI Offline Conversions API Snapchat Offline Conversions API TikTok Offline Events API
Lead Conversions	Google Enhanced Conversions for Leads LinkedIn Leads Conversions API Meta Conversions API for CRM Snapchat Leads Conversions API TikTok CRM Integrations
Custom Audience	Google Customer Match Meta Custom Audience Snapchat Custom Audience TikTok Custom Audience

Authentication

In Studio, open Sources, find the Database category, and click the Microsoft SQL Server tile.

Field	What it is	Where to find it
Authentication Name	Your own label for this set of credentials.	You choose it. Name it for the database it belongs to.
Username	The database user Signals connects as.	Created in the database by your administrator.
Password	The password for that user.	Set when the user was created.

SQL Server supports both Windows and SQL authentication. This connector signs in with a user name and password, so the account has to be one SQL Server can authenticate directly.

Caution

Create a dedicated read-only user for Datahash rather than reusing an application or administrator account. It keeps the connection working when someone rotates a password, and it stops the connection seeing more than it needs. Enter the password on the Datahash connection screen only.

Access takes two objects at two levels, and holding one without the other is the most common way this connection fails:

CREATE LOGIN datahash WITH PASSWORD = '...';
USE appdb;
CREATE USER datahash FOR LOGIN datahash;
ALTER ROLE db_datareader ADD MEMBER datahash;

The login is a server-level object and carries the account through authentication. The user is a database-level object inside appdb and is what the login maps to there; without it the server declines to open appdb for that login rather than admitting it to an empty view of the database. Membership of db_datareader is a third step, and that is the fixed database role holding read access to every user table, which is why a table added next month needs no further grant.

ALTER ROLE ... ADD MEMBER is the form available from SQL Server 2012 onward; before that the statement is EXEC sp_addrolemember 'db_datareader', 'datahash'. On Azure SQL Database, where USE does not work, run the login statement on a connection to master and the remaining two on a connection to appdb. Azure also accepts a contained database user, CREATE USER datahash WITH PASSWORD = '...', which lives entirely inside appdb and needs no server-level login to pair with.

An installation left in Windows Authentication mode turns away every SQL Server login it is offered, correct password included. Moving it to mixed mode is a server property that takes effect when the SQL Server service restarts, which makes it a maintenance window rather than a setting.

Configuration

Instance Setup

Select the Integration Type, which is the kind of data your records hold. Offline Events is the common choice. Give the instance a name, then choose the table path or the query path. On either path you enter the Host Name, Port and Database Name, and then either the Table Name or a SQL query.

The integration type decides the columns Signals expects and which destinations the connection can feed, so it is worth settling before anyone models the data. It also decides which file format you get on the next screen.

Finding the port of a named instance

1433 is the default instance’s port. A named instance takes a dynamic port at startup instead, and it can be a different one after each restart; clients that know the instance name normally ask the SQL Server Browser service over UDP 1434 for the current number. This form takes a host and a port rather than an instance name, so the instance needs a port that holds still.

Assign the named instance a static TCP port in SQL Server Configuration Manager and enter that number, having first confirmed the TCP/IP protocol is enabled for the instance. The instance has to be restarted before the new port takes effect, so a test run before that restart fails and says nothing about whether the port was set correctly. UDP 1434 then does not have to be reachable, which suits a server where the Browser service is switched off as a matter of policy.

Table path

Enter the connection details including the table name, then complete the setup. The name is typed rather than picked from a list, so it has to match the database exactly.

Query path

Enter the connection details without the table name, then write the query. Preview the results before finishing, and read the column names in the output rather than just the row count: a query that runs but returns the wrong names will fail silently once the connection is live.

Manage instance

One instance reads one table or query for one integration type. To read another, add another instance from the Manage existing instance table. To change an existing one, open it from there, click the edit option in the menu to the top right, update the fields and click Finish.

File format and schema

Download the file format from the setup screen and shape the table to it. The column names are what Signals matches on, so a column that has been renamed or cased differently is not recognized and the row it belongs to is dropped rather than reported.

On the query path the same rule applies to the column aliases your query returns. Aliasing to the expected names is the usual way to reconcile data you cannot or should not restructure.

Data & identifiers

Personal identifiers are normalized and hashed before they are sent to a destination.

Best practices

• Use a dedicated read-only user scoped to the data Datahash reads, rather than an application account.
• Prefer the query path with explicit column aliases over pointing at a raw table. It lets your team change the underlying model without breaking the connection.
• Establish that a secondary is a readable secondary before nominating its host. An Always On availability group turns client sessions away from a secondary replica until that replica is set to ALLOW_CONNECTIONS = READ_ONLY or ALL in its secondary role, and the setting is available on Enterprise Edition: a basic availability group, which is what Standard Edition offers, permits no read access to its secondary whatsoever. A database mirroring partner is further off still, because the mirror database stays in a restoring state and yields rows only through a snapshot created against it. None of that describes Azure SQL Database, which gives you neither availability group replicas to administer nor mirroring, so ask your Datahash representative what this connection can be pointed at there rather than reading the paragraph above as applying to it.
• Use a SQL authentication login rather than a domain account, since the connection comes from outside your network.
• Clean the data in the view or query: lowercase email, phone in E.164, no stray whitespace. Hashing a badly formatted value produces a hash that will never match.
• Rotate the password periodically and update the connection afterwards.

Troubleshooting & FAQ

The connection times out rather than reporting bad credentials. That points at reachability rather than authentication. Confirm the host is exposed, the port is open, and any firewall or allow list permits the connection.

Credentials are rejected. Check the user can read the data from a client of its own first. Most failures here are permissions rather than typos.

The table name is rejected. It is typed rather than selected, so it has to match exactly.

The query previews correctly but nothing is delivered. The column names it returns do not match the expected schema. Alias them to the names in the downloaded file format.

The instance cannot be found. Named instances are not addressed by name here. Use the host and the port that instance listens on.

The account clears authentication and the connection fails anyway, naming the database. A login with no matching user inside appdb cannot open it, and the message says as much: that the database requested by the login could not be opened, and that the login failed. The second half of that sends people to the password, which was never the problem. Run CREATE USER datahash FOR LOGIN datahash against appdb, and confirm the name is in sys.database_principals there and not only in sys.server_principals at the server.

Every table behaves as though it is missing. An account without read privilege gets a permission failure rather than a missing object: the message reports that the select permission was denied and names the object, schema and database it was denied on. Usually that is the db_datareader membership never granted. Where the role is in place and one table alone refuses, look for a DENY on it, because a deny outranks the role’s grant and leaves a single table unreadable while the rest of the database answers normally.

It was working and stopped. The password may have been changed or the user disabled. A dedicated service user avoids this.