Connecting Payroll Data to Web

Hi,

We are currently trying to make payroll data stored on an SQL server available via a secured web site for our employees. This will allow them to view their direct deposit information by visiting a particular .asp page running on our web site. In order to enable this, we must create a connection to the SQL server. In order to verify the connection, we have created an Access ADP that will allow us to test our connection parameters. Once we can connect in Access we can write a connection string for use in our .asp pages. We believe that the following connection parameters should connect to the SQL server:

Server Name: hsa1.***.com:1433
User Name: payroll-web
Password: ***
Database: HSA

We have already created the payroll-web user name on the SQL server and given it permissions to access the HSA database running on the SQL server. However, when we create an Access ADP using these settings we receive the following error:

"Test failed because of an error in initializing provider. [DBNETLIB][ConnectionOpen(ParseConnectParams())]Invalid connection."

At this point we are unaware of the cause of this error and have tried several different workarounds, none of which have worked:

Replaced hsa1.***.com:1433 with hsa1.***.com, 1433
Replaced payroll-web and *** with Administrator and ***
Removed HSA
Selected NT/Challenge Response Security instead of provising a user name and password

Does anyone have any ideas as to (i.) why this error is occuring and (ii.) what we can do to solve it and connect to the SQL Server using an Access ADP? Thanks so much for your help!

Warm Regards,

Kristopher A. Tillery
Director of E-Commerce, Harvard Student Agencies

Email: tillery@.fas.harvard.edu
Phone: +1 603 205 0228
Post: 359 Quincy Mail Center, Cambridge MA
Web: http://www.hsa.netI would suggest (on the machine acting as the client, probably your web server) that you use the SQL Client Configuration Tool to change the default network protocol from its current value of Named Pipes to TCP/IP.

-PatP|||Hi,

Thanks for the response. On the SQL Server we have don't have a "Client Configuration Tool" but we do have a "Client Network Utility." that is available in the SQL Server program group on the Start Menu. This utility shows that TCP/IP is already enabled as the first-choice protocol. Any ideas? Thanks again.|||I'm not sure, but according to msdn, your server doesn't exist. http://support.microsoft.com/default.aspx?scid=kb;en-us;275118

When you created the ADP, didn't you provide the server information at that time?|||The [DBNETLIB] in your error message indicates that the connection is being made using the Named Pipes protocol library.

Your hsa1.***.com:1433 server name is in DNS format. Typically, server names in this context are in WINS format so that the enumerator can find them without forcing a DNS resolution. It is also quite uncommon to have the database on a DMZ server, the database typically resides inside the firewall. How confident are you that the name you are using is correct for your database server?

Which version of MS-Access are you using? Have you applied the most current set of service patches to it from Microsoft?

If your SQL Client Network Utility specifies TCP/IP as the preferred protocol (listed at the top of the right hand protocol list, and there isn't a defined alias (on the second tab) for your server, then I'm not sure why you aren't using TCP/IP. That may be your whole problem, but I can't figure out what I'm missing.

-PatP|||The [DBNETLIB] in your error message indicates that the connection is being made using the Named Pipes protocol library.

Your hsa1.***.com:1433 server name is in DNS format. Typically, server names in this context are in WINS format so that the enumerator can find them without forcing a DNS resolution. It is also quite uncommon to have the database on a DMZ server, the database typically resides inside the firewall. How confident are you that the name you are using is correct for your database server?

Which version of MS-Access are you using? Have you applied the most current set of service patches to it from Microsoft?

If your SQL Client Network Utility specifies TCP/IP as the preferred protocol (listed at the top of the right hand protocol list, and there isn't a defined alias (on the second tab) for your server, then I'm not sure why you aren't using TCP/IP. That may be your whole problem, but I can't figure out what I'm missing.

-PatP|||Create a UDL file on the desktop of the client machine, form a valid connection, test it, and then open this file in notepad.

Client Network Configuration on SQL box has nothing to do whether the server listens on TCP or not. This is configured using Server Network Utility on the SQL box.|||Client Network Configuration on SQL box has nothing to do whether the server listens on TCP or not. This is configured using Server Network Utility on the SQL box.True, but based on the error message the connection process hasn't gotten far enough that it matters if the SQL server is listening or not... At least as far as I can tell, we're still trying to get the client machine to that point in the process.

-PatP|||Your hsa1.***.com:1433 server name is in DNS format. Typically, server names in this context are in WINS format so that the enumerator can find them without forcing a DNS resolution. It is also quite uncommon to have the database on a DMZ server, the database typically resides inside the firewall. How confident are you that the name you are using is correct for your database server?

Which version of MS-Access are you using? Have you applied the most current set of service patches to it from Microsoft?

If your SQL Client Network Utility specifies TCP/IP as the preferred protocol (listed at the top of the right hand protocol list, and there isn't a defined alias (on the second tab) for your server, then I'm not sure why you aren't using TCP/IP. That may be your whole problem, but I can't figure out what I'm missing.

Thanks for this message. I'm afraid this conversation has surpassed my technical knowledge a little bit. Please explain DNS vs. WINS format.

What I do know is that hsa1.***.com resolves to the IP address of the SQL server. If desired, I can use the IP address of the server directly but this doesn't prevent the original error.

I am using the most recent version of Access and connect to other SQL servers (externally hosted) regularly.

My server does specify TCP/IP as the top-right selection in the Client Network Utility. There is no defined alias in teh second tab. I don't understand what you mean by "why you aren't using TCP/IP," I thought I was?

This is getting confusing, but thanks so much. I look forward to hearig from you.|||Create a UDL file on the desktop of the client machine, form a valid connection, test it, and then open this file in notepad.

Client Network Configuration on SQL box has nothing to do whether the server listens on TCP or not. This is configured using Server Network Utility on the SQL box.

Thanks for the message. Do you have any more specific instructions on the creation of a UDL file? I am not familiar with this process. Also, do you know how to access the Server Netwok Utility on the SQL server? It's not listed in the SQL Server program group. Does it work within another program? Do I need to install it? Is there a different way to access it?

Thanks!|||Create a new TEXT document on your desktop by right-mouse-clicking on it, selecting New, Text document. Change the extension from .TXT to .UDL and you'll see how the default TEXT icon will change to Microsoft Data Link icon.

For SQL Server Network configuration look for svrnetcn.exe. Most likely it's in "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\svrnetcn.exe"|||Because hte SQL Client Network Utility has no alias for your server, and shows TCP/IP as the preferred network protocol (the top protocol in the enabled list), I would expect connections to be made using TCP/IP unless something intervened. This intervention could happen at the DSN (http://searchvb.techtarget.com/sDefinition/0,,sid8_gci874018,00.html) level, or in a connect string (http://support.microsoft.com/default.aspx?scid=kb;en-us;193128) to override the default library specified in the Client Network Utility.

The [DBNETLIB] string in your error message means that the client machine is trying to connect via the Named Pipes protocol library. That makes me think that something must have overridden the default protocol library and specified the use of Named Pipes. Unfortunately, I don't have a clue where that might have happened.

Since you regularly connect to other SQL Servers, I think that the SQL connectivity has to be Ok. What exactly do you mean by the phrase "externally hosted SQL Server" (I just want to be sure I'm not making unfounded assumptions)?

As a separate issue, I'd like you to connect to the SQL server using another tool than what you have been using, preferably SQL Query Analyzer. This will confirm that the client machine can connect successfully to the SQL Server at will, which would help to put many of my other questions to rest.

If you are interested in the difference between DNS (http://searchnetworking.techtarget.com/sDefinition/0,,sid7_gci213908,00.html) and WINS (http://searchexchange.techtarget.com/sDefinition/0,,sid43_gci214128,00.html) you can click the links for definitions. You can find additional information via MSDN (http://msdn.microsoft.com/), which is a great place to visit regularly for Microsoft related technical information.

-PatP|||If client MDAC is at 2.5 or earlier then the default library IS Named Pipes. And as a rule (AGAIN!!!), - NEVER RELY ON DEFAULTS!|||WOW:

This was not at all what I expected to fix the problem. After several hours of research, we found out through the server logs that the server was not listening on port 1433 because:

Error: 17826, Description: ods Could not set up ListenOn connection "1433". Operating system error 10013., An attempt was made to access a socket in a way forbidden by its access permissions." - This error can be solved by adding the MSSqlServer Service Account to the local administrators group.

So, we fixed the necessary permissions and are good to go. Thanks for the help.