October 24, 2002 at 6:07 pm
I'm cracking up, folks.
I have IIS 5.0 on one Win2K Pro machine (Machine A), and MSSQL 2000 on a Win2K Server machine (Machine B), both on my local network.
I was making some changes to my network because I was swapping out my satellite internet connection for DSL. The changes to my network amounted to changing the IPs of these two machines. Then I got a Linksys router between the DSL and my network, to allow me to do VPN stuff and expose my IIS server to my clients for demos. Like today's demo -- which went down in flames.
Suddenly my ASP pages on Machine A now are coughing up the error "SQL Server does not exist or access denied" for non-NT authenticated users when they try to do DB calls. I do not have a domain here, just a workgroup, and I have never used NT authentication before with this site. If I turn off anonymous users, I can get in from my local machine -- but I have the same username/password on both machines, so it's apparently using that. If I turn on anonymous logins, then ALL users (me included) get that error message.
My connection string is unremarkable:
Provider=SQLOLEDB.1; OLE DB Services=-1;User ID=sqlusername; Password=sqlpassword; Initial Catalog=mainDB; Data Source=MachineB\Instance1
Parts of the site that don't do DB calls work fine -- but all DB calls fail.
I tried all variations of the IIS security settings. I need to have "Integrated Windows Authentication" turned on for it to work from Machine A. BUT...if I have that on *and* Anonymous Access both turned on, it fails on Machine A. (Having Basic Authentication on or off makes no difference.) But no matter what settings I choose, NOTHING works from a machine off of my network. Users can see the login page, but when they submit it, they wait, and they get the error message.
Any ideas? Anyone?
-Tom
October 24, 2002 at 6:36 pm
quote:
I'm cracking up, folks.
Responding to my own message here...
Big question that's rolling around my brain. All the MSDN articles I read tell me that you need to have the same account on both machines, or in the domain controller, etc. But...if my connection string is explicitly using a set of MSSQL username+password credentials...then who cares what Win2K account the IIS server is working with? That sounds to me like, no matter whether you set your MSSQL authentication to Windows-only or SQL+Windows, well, it's really JUST Windows.
Thanks in advance for your thoughts on this...
- Tom
October 25, 2002 at 6:12 am
I turned on auditing for failures, looking for some more detail on why the connection is failing. Nothing shows up in either the Win2K event logs or the MSSQL logs. So I turned on auditing for both failures and successes, and the successes ARE logged by MSSQL.
So maybe the first half of the error message ("SQL Server does not exist...") is in some way pertinent? Obviously it does exist, but maybe my IIS machine is unable to see it out there?
October 25, 2002 at 8:07 am
At your IIS machine, have you tried to create a SUer or system DSN? I will try that to see if your web server is being able to connect to the SQL Server. Another question, are you usiong Windows authentication or Both? This is a permission issue from the web server to the SQL Server. I recommend to check those two and then you can pin-point to the problem in hand. Can you ping the SQL Server from your Web server?
October 25, 2002 at 2:34 pm
quote:
At your IIS machine, have you tried to create a SUer or system DSN? I will try that to see if your web server is being able to connect to the SQL Server.
I know that the two machines can communicate. For instance, I wrote a VB application using precisely the same connection string and ADO objects and calls, and it connects without problem. Obviously, it is connecting with "my" permissions, and I have the same account and password on both boxes.
I would rather not create a User or System DSN, as this project is for a client and after 2 years of working on it, this is the first time this problem has come up. I would rather not introduce a special database-calling mechanism just for my own development environment. We're standardized on ADO/OLEDB on my development box, my client's, and the production server.
quote:
Another question, are you usiong Windows authentication or Both? This is a permission issue from the web server to the SQL Server. I recommend to check those two and then you can pin-point to the problem in hand.
I have tried both, and neither makes the problem go away.
quote:
Can you ping the SQL Server from your Web server?
Yes.
October 28, 2002 at 12:17 am
Have had the same problem over here, solved it finally after long long time.
What you do is this. Install client tools of SQL server on a test machine in the same network on a machine that had never SQL server on it. Be sure that on the server authentication is set to both SQL and Windows authentication. Login in on the new machine (client) and use SQL authentication to connect with the Server and use username: sa and corresponding password. If it is working format your other client and install windows 2000 again. Install SQL server client tools (or server) and connect. You'll see it will work. Registry is full of old settings.
Let me know it it worked,
Regards, D.J. de Groot
October 28, 2002 at 7:26 am
quote:
...If it is working format your other client and install windows 2000 again.
Ouch.
quote:
Registry is full of old settings...
Any way of simply determining what the bad registry settings are and setting them right? This is a pretty heavy cure for the problem. Kind of like tearing down and rebuilding your whole house because the kitchen faucet is leaky.
Not to sound thankless, though. I appreciate it. It's good to know I'm not the only person who has dealt with this. So you're saying, to clarify, that I should format and rebuild my Machine A (the Win2K Pro client). My Machine B (the Win2K Server/MSSQL machine) can stay the same, right?
October 28, 2002 at 9:29 am
Hello tkc,
This may not be entirely relevant to your
exact situation, and I'm not too up on IIS, but here goes...
I had this same error come up a couple of weeks ago on an installed, working, production server.
Here's the config: A remote location using dial-up to connect to a frame relay corporate network. The remote has a local MSDE instance (SQL 2K sp2) with client tools installed. Once the connection is established, the user initiates a "manual replication" process to copy info from two local tables to a database residing at corporate HQ via a linked server definition in the local MSDE (why I'm not using native SQL replication is another discussion). Like I said, everything's cool & working, one day out of the blue this same message ("does not exist... access denied") pops up. I was assured that the customer had changed nothing in regards to network settings, configuration, etc., as far as the local sys admin knew.
Using SQL Query Analyzer from the remote back to corporate works fine, same in Enterprise Manager. Application bombs with this message. Same "unremarkable" connection string. A colleague asks if I can ping the corporate SQL server from command prompt on the remote. Lo & behold, I cannot. Everything worked fine in SQL QA & EM, but when my app went to connect, I needed to explicitly specify the fully qualified machine name (computer.domain.com) connection string as "Server=". Fortunately, I have a RemoteServerName column in one of my setup tables to specify this, so my connection string ends up changing from "Server=SIMNAPP1" (SIMNAPP1 is the corporate server) to "Server=SIMNAPP1.domainname.com" and everything's tickety-boo. BTW, everything's using SQL authentication.
>>>
So maybe the first half of the error message ("SQL Server does not exist...") is in some way pertinent? Obviously it does exist, but maybe my IIS machine is unable to see it out there?
>>>
I don't know from IIS, but it looks like your ASP pages can't resolve the SQL server's location, have you tried explicitly stating "Server=" in your connection string? Also, I'm thinking that maybe since you changed IP addresses, your IIS machine can no longer resolve the location of the SQL server?
Anyhow, hope this helps, good luck.
Vik
October 28, 2002 at 11:24 am
hi tkc,
I know it seems ridiculous to format your pc for a thing that should be easy, but microsoft made this big mistake in its software. I guess you also could delete all registry settings related to SQL server. I was thinking about doing this but decided to just format the (for me) relatively insignicficant client. It took about one and half hour in total.
Maybe convert to Postgres SQL 🙂
Yes leave the server intact and only format the client machine.
Regards, deejay
October 28, 2002 at 11:55 am
quote:
I know it seems ridiculous to format your pc for a thing that should be easy, but microsoft made this big mistake in its software. I guess you also could delete all registry settings related to SQL server. I was thinking about doing this but decided to just format the (for me) relatively insignicficant client. It took about one and half hour in total.
I can understand. For me, it mean be reinstalling all the software and settings on my main development machine. Pretty hefty investment of time.
quote:
Maybe convert to Postgres SQL 🙂
Always an option!
quote:
Yes leave the server intact and only format the client machine.
Thanks for your help with this...
October 28, 2002 at 4:36 pm
tkc,
I had a similar problem about two months ago. I solved it by downloading two free utilities from http://www.sysinternals.com
You want to get Regmon and Filemon. These two programs will monitor your registry and system files respectively.
In my situation, I was running Regmon on my client machine and then attempting to connect to my SQL Server. Analyzing the contents of the Regmon output file revealed a permissions issue on a particular registry key. I reset the permissions on the key and that solved the problem.
Might be worth a shot before you re-image the pc.
Best of luck.
wavie davie
October 28, 2002 at 7:00 pm
Here is what I have done on my web servers when I have experienced your issue. I know you don't want to create a DSN, but please read on...
Create a system DSN and use the "test data source" button to ensure you can see the database server.
Delete the system DSN you created above.
That's it. It appears that this must initiate the proper network libraries to connect to the database server.
Heath
October 30, 2002 at 3:38 am
Yo wavie davie,
that is an excellent tool you've got there ... on my way to sysinternal ... (!)
will post my finding, if any
bye now , deejay
October 30, 2002 at 9:37 am
Thanks Deejay!
Indeed, these two tools have saved my skin on more than one occaision. They are definitely worth having!
Best of luck!
wavie davie
October 30, 2002 at 1:03 pm
yo wavie davie,
it's an absolute must-have !!!!
Installed it and seen it working, it is amazing. real-time registry entries !
The program is only 1.3 mb !
Thanks for this cool feature,
Deejay
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply