acessing data from sqlserver

[venugopal jd]

even when the odbc dsn is configured with the system administrator account
i get an error message that the user is not a valid user for trusted connection. the code works fine for acess.the sql server is configured for mixed security.we get the error message even when i configure the domain administrator account as the anonymous account the account has sa privilages on the sql server and has all requisite permissions for the database.

[Julian Everett]

On 10/5/98 8:19:33 AM, venugopal jd wrote:
> even when the odbc dsn is configured with the system administrator
> account
i get an error message that the user is not a valid user for
> trusted connection. the code works fine for acess.the sql server is
> configured for mixed security.we get the error message even when i
> configure the domain administrator account as the anonymous account the
> account has sa privilages on the sql server and has all requisite
> permissions for the database.

This sounds as though your anonymous user passwords are out of sync. You are correct to create a new anonymous user for integration with SQL Server - if you add IUSR_machine to your SQL app NT workgroup and try to create a login from Security Manager it will never map because the account name contains an underscore (mapped login is null when you check with xp_logininfo). IIS requires NT validation for every process it performs. Under Anonymous Access, the web server impersonates the IUSR account by supplying the password specified in its configuration. If you reconfigure a different account for anonymous access then you must explicitly enter the password for that account. Otherwise IIS uses the new user name with the old password, and you get the error that the user is not valid for a trusted connection. Hope that helps.

[George Gadbois]

I think this problem is caused by the use of mixed security on SQL Server. Try creating a user on SQL Server and then use that UID for all queries so that SQL Server does not need to refer to the NT domain security which seems to cause problems. This is the method I use to avoid SQL Server access problems.

I think you will take a performance hit making web server connections with mixed security set up. If you have a low volume site and expect multiple data accesses per user, use session level connections. Session level connections use a lot of web server RAM which does not appear swappable to disk. It will not take many connections to run your server out of RAM. Been there, done that. With a high volume of connections use ODBC connection pooling.

Regards,

George

[Sandi]

We are having the same problem here and have done all the suggested fixes. Nothing works
so far. Has anyone found anything else to fix this problem??

On 12/10/98 10:43:06 PM, George Gadbois wrote:
> I think this problem is caused by the use of mixed security on SQL Server.
> Try creating a user on SQL Server and then use that UID for all queries so
> that SQL Server does not need to refer to the NT domain security which
> seems to cause problems. This is the method I use to avoid SQL Server
> access problems.

I think you will take a performance hit making web
> server connections with mixed security set up. If you have a low volume
> site and expect multiple data accesses per user, use session level
> connections. Session level connections use a lot of web server RAM which
> does not appear swappable to disk. It will not take many connections to
> run your server out of RAM. Been there, done that. With a high volume of
> connections use ODBC connection pooling.

Regards,

George

[George Gadbois]

I should have read your question more carefully. When you create the DSN, make it a System DSN and configure for logon to SQL Server with UID and password. If you configure for using a trusted connection that is the only way to connect from the web and it probably won't work with mixed security. At least, I don't know how to make it work.

At DSN creation, use the SQL Server UID and password you plan to use for queries for testing the connection. It is difficult to configure access to SQL Server for the anonymous user.

If you are not using the Microsoft ver. 3.5 ODBC drivers, I suggest that you download them.

Here is a working example from my global.asa file. Because I have a small number of users I am using a session level connection. This technique gives the users very quick response after the first query. It also uses a lot of server RAM. You could also open this connection on each ASP page and close it after using it. As I understand how connection pooling works, you should close the connection as soon as possible, but do not set it to Nothing. Let the ODBC driver destroy the connection after the appropriate time out. For connection pooling to work, you must also use the same UID and password for all queries. Security requirements may not allow this.

global.asa example:

<SCRIPT LANGUAGE="VBScript" RUNAT="Server">
'First implementation 15 July 1998
'New mixed SQL Server security makes connecting slow
'Revised 8 Sep 1998 decreased connection timeout to 15 min because of insufficient RAM problem on server

Sub Session_OnStart

Session.timeout =15 'time in minutes
Set oConn =Server.CreateObject("ADODB.CONNECTION")
Set Session("oConn") = oConn
oConn.Connectiontimeout = 50 'time in seconds
oConn.Open "DSN=epsdata;UID=yourUID;PWD=yourpassword"

End Sub

Sub Session_OnEnd

oConn.Close
Set oConn = Nothing

End Sub

</SCRIPT>

George
gadboisg@redrose.net


On 10/5/98 8:19:33 AM, venugopal jd wrote:
> even when the odbc dsn is configured with the system administrator
> account
i get an error message that the user is not a valid user for
> trusted connection. the code works fine for acess.the sql server is
> configured for mixed security.we get the error message even when i
> configure the domain administrator account as the anonymous account the
> account has sa privilages on the sql server and has all requisite
> permissions for the database.