Category: Microsoft SQL Server

Help And Howtos For Microsoft Sql Server

Microsoft SQL Server is Microsoft’s relational database management system (RDBMS). Microsoft originally worked with Sybase on earlier versions but later parted company and Sybase renamed their version more recently to Sybase Adaptive Server Enterprise. The current version of SQL Server is SQL Server 2008 R2.

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.

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.

Read More ?

Check Index Fragmentation with SQL Server 2000/2005

This post looks at how to check if an index is fragmented in a Microsoft SQL Server table and how to rebuild the index. The advice offered in this post works for both SQL Server 2000 and SQL Server 2005 although the methods are deprecated for SQL Server 2005 and are likely to be dropped in the next release. I will look at the alternate way of doing this for SQL Server 2005 in a later post.

Read More ?