This post is a follow up to yesterday’s post titled "Export data from SQL Server to a tab file with bcp". In this post we will again export data from a SQL Server table into a tab file but this time will select which data we want to export. This means you can export just a subset of data from a large table. You could also join multiple tables and export to one file.
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.
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 …
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 …
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).
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.
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.