Text Fields

Questions sometimes arise over large varchar types (anything larger than varchar(255)) that became available with Microsoft SQL Server 7.0. When accessing long varchars with TDS protocol version 4.2 or 5.0, these fields will be truncated to 255 characters, due to limitations inherent in the protocol definition. Your best bet in that case is to convert them to text types.

In Microsoft SQL Server 7.0 and later, varchar types can hold up to 8000 bytes (8000 ASCII characters or 4000 Unicode characters). To move these large varchars through TDS 4.2, convert them with either a CONVERT as in,

SELECT mycol = convert(mycol, text) FROM mytable 

SELECT CAST(mycol as TEXT) FROM mytable

A related problem is that some people have reported problems with text field using TDS version 7.0. One known workaround is to convert long strings to varchar(8000) in your query text with CAST( variable_name as varchar(8000) ) as variable_name. Text datatype handling is fixed in FreeTDS 0.60, except for bcp operations.

There is also a bug ("Lions and tigers and bugs! Oh, my!") in Microsoft's implementation of text fields. Disregardless [sic] of their documentation, you must explicitly set the value of TEXTSIZE, else the text fields will be represented to have a maximum size of 4 gigabytes or so. The usual manifestation is some sort of spurious "out of memory" error or segment fault. To avoid this, set TEXTSIZE to some reasonable value before querying any TEXT fields. For example, in isql:

1>set TEXTSIZE 10000
2>go

Endianism

If either your server or your client is a big endian system, pay careful attention to all references to endianism anywhere near FreeTDS. See the section on Little Endian Emulation for details.

Datetime and Money

Big endian clients may experience difficulty with Microsoft servers. Some versions of SQL Server 7 did not handle these types on these machines correctly, according to the protocol. According to http://support.microsoft.com/support/kb/articles/Q254/1/23.ASP on the Microsoft support site, it's fixed as of service pack 3. Unfortunately, there's no direct way for FreeTDS to know whether or not a service pack has been installed, and how/whether to support the buggy version is an outstanding issue. Your best bet is to apply their patch.

The Knowledge Base article states "The Sybase CT-Lib client is the only known big-endian client that can connect to SQL Server." Depends on who's doing the knowing, of course.

Microsoft's "Integrated Security"

FreeTDS may be unable to connect to the server. The error message that appears will be "Login failed for user 'example'. Reason: Not associated with a trusted SQL Server connection". To solve this, turn on SQL Server authentication:

• Open the SQL Server Enterprise Manager,

• Select the server,

• Right mouse click and choose Properties. A properties window will appear.

• Choose the Security tab. The security properties will be displayed.

• Change the Authentication field to SQL Server and Windows,

• Apply the changes and try again.

These instructions apply to Microsoft SQL Server 7 and SQL Server 2000.

SQL Server's Security Model

Microsoft supports two security models in three permutations:

• Windows NT Authentication Mode. The operating system performs authentication; users will not have explicit SQL Server accounts.

• Standard Mode. SQL Server authenticates connections itself without consulting the operating system. Users of course need SQL Server accounts to log in.

• Mixed Mode Combines the above two.

For normal operation, you need either Standard or Mixed mode.

"Windows NT Authentication", often called "integrated security", relies on Microsoft's domain login. In it, a user's network security attributes are established at network login time. When connecting to the database server, SQL Server uses the facilities of the host operating system (Windows NT or similar) to determine the authenticated network username. SQL Server then permits or denies login access based on that network username alone, without requiring a separate login name and password.

The FreeTDS supports integrated security mode. If you have SQL Server running in integrated (domain) mode along with a Windows PDC, and wish to try it, see Domain Logins in the Advanced Configurations chapter.

FreeTDS supports the traditional database security model, which Microsoft terms "SQL Server Authentication" but is frequently known as "standard security". Username+Password pairs have to be passed to the server explicitly.

Mixed Mode allows users to connect using either authentication method. Users who connect through a Windows NT account can make use of trusted connections in either Windows NT Authentication Mode or Mixed Mode. After successful connection to SQL Server, the security mechanism is the same for both modes.