defiantclass1
Posts: 24
|
| Posted: 12/15/2005, 5:12 AM |
|
Hello,
I'm having a terrible time trying view a page in Live mode.
I have built an ASP page, which is connected to an SQL Server database on another machine. My connection tests successfully and I can access the database tables and fields, obviously, as I have already created the page with them.
When I try to view in the Live Mode I get the following:
Error information:
Microsoft OLE DB Provider for SQL Server (0x80004005)
[DBNMPNTW]Access denied.
Other possible cause of this problem:
The database cannot be opened, most likely due to incorrect connection settings or insufficient security set on your database folder or file.
For more details please refer to http://support.microsoft.com/default.aspx?scid=kb;en-us;Q306518
I have read the MS article referenced, but it refers to many possibilities, in none of which could I find the answer. I have tried a number of username/passwords in my connection and bumped up the permissions of them in the SQL db, both in the Security area and on DB itself.
I have also read the other very similar postings and I have tried different things I found there. Just can't seem to get around this problem.
|
 |
 |
mrachow
Posts: 509
|
| Posted: 12/15/2005, 6:21 AM |
|
Have you an IIS running installed on a client Windows?
It's possible that the User under which IIS is running do not have the permissions to access the folder where the OLE DB dlls are located.
_________________
Best regards,
Michael |
|
|
|
Dave
|
| Posted: 12/15/2005, 6:27 AM |
|
Yes, thank you!
I'm actually seeing a bit of that now. I did just change the Anonymous access to another domain user account. I do seem to be able to view the Live page now. However, I cannot view the live page when I include that same user (as in IIS) in my connection string). So far, only if I use the sa user in the string, can I view the Live page.
But I do have the same user (as I have enter in IIS) as a user with permissions in SQL. I tried to mimic the permissions for that user against the sa user in SQL.
But, I will say, I can view a Live page at the moment. Just not completely sure why. Thank you for responding so quick!
|
|
|
|
Paul Shearing
|
| Posted: 12/16/2005, 11:43 AM |
|
In my experience this whole area is a can of worms. This is how I deal with it, Hopefully, the following may include pointers that will help other with issues in this area.
1) Make sure that the SQL 2000 server is running in mixed mode. Not sure? Run RegEdit on the Server and have a look at:
HKLM\Software\Microsoft\Microsoft SQL Server\InstanceName\MSSQLServer
If you are not using Instances then then ignore (remove) the \InstanceName part of the above, else substitute the name of the instance. Look at the DWORD key named LoginMode. I always have this set to 2 (Mixed Mode). A discussion of the meaning of this can be found, amongst others at: http://www.windowsecurity.com/articles/SQL_Server_2000_Authentication.html)
The important part of this is that you can now connect to the server using a nominated user (eg sa) and password.
2) The next thing is to create (via Enterprise Manager) a user and password for accessing the required database. You use these when building your CCS Connection. You can use sa but it is not recommended.
3) Finally, the server name. I always use an alias. Some think that this is inefficient but I've never noticed a performance hit - I'm sure it gets cached in RAM so a fer microseconds per query isn't going to notice. Say your production server is named "Alpha" and your development server (or workstation) is named "Beta". When developing with CCS you would connect to "Beta". On the Production server, create the following registry key:
HKLM\Software\Microsoft\MSSQLServer\Client\ConnectTo
add a string key with the following characteristics:
Key Name "Beta" i.e. the name of your development server/workstation
Type: REG_SZ
Data: DBMSSOCN,Alpha
So that the key looks like:
Beta REG_SZ DBMSSOCN,Alpha
This maps the server name "Beta" to the actual server "Alpha".
The options are:
Instead of the name of the server in the Data, you can use the IP Address:
Beta REG_SZ DBMSSOCN,192.168.44.104
This is more efficient because it precludes the requirement for a DNS resolution. If your server has an instance then include it in the data with an interstitial backslash:
Beta REG_SZ DBMSSOCN,Alpha\InstanceName
or
Beta REG_SZ DBMSSOCN,192.168.44.104\InstanceName
Note that there is no leading double-backslash for the server name in this context.
DBMSSOCN is the Database Microsoft Socket Connecior. TCP/IP to you and I.
Use DBNMPNTW for the named named pipes protocol. I have found that DBNMPNTW often works where DBMSSOCN is gibing problems. Oftenthis is because TCP/IP traffic is so heavily constrained via firewalls and other safety-concious obstructions.
Using this ruse, you do not have to touch your CCS connection because on the production server the alias efficiently maps "Beta" to "Alpha" in real-time. Furthermore, if things go pear=shaped with the database on Alpha you can point to a different (backup) server just by changing the one registry key.
The other advantage is that you are removed from the vagaries of integrated mode whereby users have to be part of the domain. Yuk.
I use this for all my database access software. It all talks to a server called SDS_SERVER and the alias maps this to the real SQL server on each client's site.
KISS - Keep it simple squire.
Paul Shearing
Software Development Services
Merrie England
|
|
|
|
|
