Connect to SQL Server through an SSH tunnel

I recently needed to connect to a Microsoft SQL Server which I couldn’t reach directly, but which is visible from a web facing Linux machine. I’ve covered how to create an SSH tunnel on Windows with PuTTY already and then it’s simply a matter of connecting through the tunnel on the localhost to the SQL Server at the other end.

Describe table structure with MS SQL Server

This is the second in a series of three posts about using the sp_tables, sp_columns and sp_stored_procedures stored procedures with Microsoft SQL Server databases. This post is about sp_columns which is used to describe the table structure of a SQL Server table. The simplest way to use sp_columns to show the columns and related information …

Read more

Get a list of tables with MS SQL Server

I’ve recently been doing some work with Microsoft SQL Server but the server itself for this particular customer is behind a firewall and I have no way currently of connecting with the SQL Server tools, so have to access it via some PHP scripts running on a webserver inside the network. This is the first …

Read more

The user is not associated with a trusted SQL Server connection

Microsoft SQL Server allows you to connect using “windows authentication mode”, which means using Windows logins, and “SQL Server AND windows authentication mode”. In you create a SQL Server user and don’t have the SQL Server authentication mode enabled, you won’t be able to log in using that login, and will get error message 18452: “The user is not associated with a trusted SQL Server connection”. This post looks at the error message and how to enable the appropriate authentication mode.

The user is not associated with a trusted SQL Server connection

When you attempt to log in with your SQL Server login you will receive the error message as shown in the screenshot below, despite entering the correct server, login name and password. If your login credentials are correct and you are being denied access then it’s likely that the server is configured to only accept Windows Authentications. This is the default for SQL Server Express Edition (it may be for regular SQL Server as well, but I don’t know for sure).

user is not associated with a trusted sql server connection

Change to SQL Server and Windows Authentication Mode

To change from just windows authentication mode to both windows authentication mode and SQL Server authentication mode, log into the server using the SQL Server Management tool with a user who has sufficient adminstrative rights.

Right-click the server in the object explorer and click properties to bring up the “Server Properties” window as shown in the screenshot below.

Select the “security” option, which has a red arrow next to it in the screenshot.

Change “windows authentication mode” to “sql server and windows authentication mode”

Click the OK button.

change to sql server and windows authentication mode

It will then tell you “some of your configuration changes will not take effect until sql server is restarted”
restart the service and you can now connect using sql server authentication mode.