Web SQL Q & A - 01 Nov 1999

Answers from Microsoft

Regarding remote database connection protocols (named pipes and TCP/IP sockets), which of the authentication methods is likely to fail when I use the named pipes connection?

If the user doesn't have a way to validate connections with Windows NT, standard security connections will fail in their connection attempt, and the user will receive the error SQL Server is unavailable or does not exist. You can check to see whether this is happening to you by trying to connect to a share on the machine that has SQL Server installed on it. If you’re prompted for a password, you can’t get into SQL Server until you set up a proper domain structure, or enable the guest account on the server, which I don’t recommend.

Is the Windows NT Challenge/Response (NTCR) authentication method likely to fail when I use the TCP/IP sockets connection because it can’t cache user credentials?

The TCP/IP sockets connection always works in SQL Server 7.0. The TCP/IP sockets connection doesn’t support integrated security in SQL Server 6.5 and versions prior to 6.5.

If I use integrated security for SQL Server, do I automatically have to use named pipes?

No. In SQL Server 6.5 and earlier, you can use named pipes or multiprotocol network libraries. SQL Server 7.0 supports integrated security across all network libraries.

It seems like the three variables at work in establishing remote database connection protocols are the authentication method, the security method SQL Server uses, and the connection type. If I use standard security for SQL Server, do I automatically have to use TCP/IP sockets?

No. If you're trying to avoid failures such as no domain trust, and no guest account enabled, using TCP/IP sockets is the best mechanism because it bypasses NT’s security-checking layers.

If I have standard security with named pipes as the connection type and NTCR enabled on the Internet Information Server (IIS), will the connection fail from NTCR or from not using TCP/IP sockets as the named pipe?

The connection will work as long as SQL Server enables the guest account, or the IIS IUSR_Computername (or equivalent) account works with a trust relationship to SQL Server.

What are the differences in cursor functionality between SQL Server 6.5 and 7.0, specifically in evaluating variables in the WHERE clause of the statement?

The difference in cursor functionality for variables is that in SQL Server 7.0, cursors must have variable values set at the time you open the cursor. You use this value throughout the cursor’s execution.

I have a small query and a table, T1, in Database A, which I’m replicating in Database B as T1_r. Table T1 has a primary key, which is replicated to T1_r. Another table, T2, in Database B, references T1_r through the foreign key. I get a reference violation error for snapshot-based replication. For transactional replication, the snapshot asks for MS_ (an insert procedure). How can I write a query to replicate these tables?

With the foreign key on T1_r, the query tries to link every matching row from the foreign-keyed (FK) table. When the snapshot tries to delete all the rows, if it finds any matches in your FK table, it disallows the delete because it violates referential integrity. The only solution is to alter the FK table, and drop the foreign key when the snapshot replication occurs, then add the foreign key again (assuming you know the foreign key will still work after the update). For consistency, you need to always publish the full, referentially complete set of tables. Avoiding referential integrity problems on the subscribers makes life much easier.