Connection of SQL server 2000 with ASP in IIS6.0

Dear Friend,

Please tell me how to connect sql server and ASP in IIS 6.0.Earlier in IIS 5 I have connected in SQL server with ASP with DSN connection,the same global.asa file is not working in IIS6.0.But Asp pages are not giving any error ,but they are no just taking value from Database.I mean server connection is not establishing..

Could you please help in matter

Thank you

Graceson MAthewIf you are not recieving an error, I highly doubt it is your connection string.

Connection object

Hi, I have a question, regaring connection with SQL from VB.
Which connect is the faster with DSN or DSN less ?From WROX

...DSN-less connections were slightly faster than System DSN connections. The increase in performance was nothing monumental; the greatest performance boost was a mere 13% faster with 64 concurrent requests. For one, two, or four concurrent requests, there was virtually no performance improvement. In fact, no noticeable improvement is seen in a DSN-less connection over a System DSN until there are 10 or more concurrent connections.

The reason is that when ADO attempts to connect to a database using a System DSN is must perform a lookup in the registry. This lookup, while not overly expensive, does add up, especially if there are many concurrent connections.|||I would go with what achorozy says but the overriding consideration should be how you want to control the connection parameters.

You have to get the data into the application to connect - whether this is via a DSN, hard coded, loaded into a global on initialisation...
Just depends on what you want to be configurable and how you want to configure it.
Make sure that all database access is via a common module then you will find it easy to change in the future so the decision doesn't become irrevokable.

connection is successful for empty userid

Hi,
In my application , the connection to SQLServer is established
through a DSN name, UserId and Password(both UsrId and Password are
non empty) . The "SQLConnect" function is used for this purpose. The
problem is that the connection to the Database is success when I give
an empty UserId. But this problem is not there when the UserId is
invalid(non empty).In all the cases except for empty UserId , the
connection works as expectd.
Why is it connecting for empty UserId?
Is it because that default windows authentication is used for
connection when UserId is empty?
If so is there any way to avoid this (through a C/C++ program)?
pls help
Thanks
KaimalIf the connection is success full and accessing the data base objects (table
,
view, stored procedure, etc) fails then this likely failing due to a
ownership chain problem. Recommend reading (SQL Server Books on Line) BOL
topics.
Using Ownership Chains
Logins
sp_grantdbaccess
sp_grantlogin or sp_addlogin
TSQL GRANT statement
I think what you are asking is to allow Public or Guest permission to your
database. Is this what you are after?
SQL Server's default behaviour is to lock all database objects. You have to
grant permission to the objects before they are accessible.
"Kaimal" wrote:

> Hi,
> In my application , the connection to SQLServer is established
> through a DSN name, UserId and Password(both UsrId and Password are
> non empty) . The "SQLConnect" function is used for this purpose. The
> problem is that the connection to the Database is success when I give
> an empty UserId. But this problem is not there when the UserId is
> invalid(non empty).In all the cases except for empty UserId , the
> connection works as expectd.
> Why is it connecting for empty UserId?
> Is it because that default windows authentication is used for
> connection when UserId is empty?
> If so is there any way to avoid this (through a C/C++ program)?
> pls help
> Thanks
> Kaimal
>|||> Is it because that default windows authentication is used for
> connection when UserId is empty?
>
Yes.

> If so is there any way to avoid this (through a C/C++ program)?
AFAIK, you'll need to check for the empty userid in your application code
and allow the connection only when not empty.
Hope this helps.
Dan Guzman
SQL Server MVP
"Kaimal" <rbkaimal@.hotmail.com> wrote in message
news:19aff12c.0504220154.142bf7e3@.posting.google.com...
> Hi,
> In my application , the connection to SQLServer is established
> through a DSN name, UserId and Password(both UsrId and Password are
> non empty) . The "SQLConnect" function is used for this purpose. The
> problem is that the connection to the Database is success when I give
> an empty UserId. But this problem is not there when the UserId is
> invalid(non empty).In all the cases except for empty UserId , the
> connection works as expectd.
> Why is it connecting for empty UserId?
> Is it because that default windows authentication is used for
> connection when UserId is empty?
> If so is there any way to avoid this (through a C/C++ program)?
> pls help
> Thanks
> Kaimal|||checking for empty UserId wil not help . I have to verify for the user
id depending on the authentication set in the ODBC driver. Here user is
free to set a blank id or other way round for a particular DSN.
The actal problem is that i have set a non empty UserId and Password for
a my DSN and the connection is established when i gave empty userid for
this DSN.
i would like to know about any ODBC function to this default connect.
can any thing be done with SQLSetConnectAttr function?
Thanks
Kaimal
*** Sent via Developersdex http://www.examnotes.net ***|||Allowing a "empty UserId and Password" is not a good Idea. You potentially
are opening up your SQL Server and database to security threats similar to
the Code Red virus. I think what you are after is an application base
security model. Check out the BOL sp_addapprole.
"Ragesh Kaimal" wrote:

> checking for empty UserId wil not help . I have to verify for the user
> id depending on the authentication set in the ODBC driver. Here user is
> free to set a blank id or other way round for a particular DSN.
> The actal problem is that i have set a non empty UserId and Password for
> a my DSN and the connection is established when i gave empty userid for
> this DSN.
> i would like to know about any ODBC function to this default connect.
> can any thing be done with SQLSetConnectAttr function?
> Thanks
> Kaimal
>
> *** Sent via Developersdex http://www.examnotes.net ***
>

Connection gets lost

Hi,
I have a DSN set-up to access a SQL Server database from another
machine and program.
It is basically an import manager (third party product from an
external company), when I import it loads in a few thousand records
and then after about 2 minutes I get the following error:
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not
exist or access denied.
It then reconnects within a few minutes but misses loading the record
where the error happens.
Can someone help me locate the problem and how to fix it.
The external company has no problems with loading so i can only
imagine it is our SQL Server set-up
Thanks
ArchanaHi
"archanapatelwhite@.googlemail.com" wrote:
> Hi,
> I have a DSN set-up to access a SQL Server database from another
> machine and program.
> It is basically an import manager (third party product from an
> external company), when I import it loads in a few thousand records
> and then after about 2 minutes I get the following error:
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not
> exist or access denied.
> It then reconnects within a few minutes but misses loading the record
> where the error happens.
> Can someone help me locate the problem and how to fix it.
> The external company has no problems with loading so i can only
> imagine it is our SQL Server set-up
> Thanks
> Archana
>
I would initially suspect something like your Anti-virus software,
anti-intusion software or firewall is breaking the connection e.g
http://support.microsoft.com/kb/170338/en-us
You may want to also check if you are on a recent SQL Server service pack
see
http://sqlserver2000.databases.aspfaq.com/how-do-i-know-which-version-of-sql-server-i-m-running.html
John

Connection gets lost

Hi,
I have a DSN set-up to access a SQL Server database from another
machine and program.
It is basically an import manager (third party product from an
external company), when I import it loads in a few thousand records
and then after about 2 minutes I get the following error:
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not
exist or access denied.
It then reconnects within a few minutes but misses loading the record
where the error happens.
Can someone help me locate the problem and how to fix it.
The external company has no problems with loading so i can only
imagine it is our SQL Server set-up
Thanks
Archana
Hi
"archanapatelwhite@.googlemail.com" wrote:

> Hi,
> I have a DSN set-up to access a SQL Server database from another
> machine and program.
> It is basically an import manager (third party product from an
> external company), when I import it loads in a few thousand records
> and then after about 2 minutes I get the following error:
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not
> exist or access denied.
> It then reconnects within a few minutes but misses loading the record
> where the error happens.
> Can someone help me locate the problem and how to fix it.
> The external company has no problems with loading so i can only
> imagine it is our SQL Server set-up
> Thanks
> Archana
>
I would initially suspect something like your Anti-virus software,
anti-intusion software or firewall is breaking the connection e.g
http://support.microsoft.com/kb/170338/en-us
You may want to also check if you are on a recent SQL Server service pack
see
http://sqlserver2000.databases.aspfaq.com/how-do-i-know-which-version-of-sql-server-i-m-running.html
John

Connection gets lost

Hi,
I have a DSN set-up to access a SQL Server database from another
machine and program.
It is basically an import manager (third party product from an
external company), when I import it loads in a few thousand records
and then after about 2 minutes I get the following error:
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server do
es not
exist or access denied.
It then reconnects within a few minutes but misses loading the record
where the error happens.
Can someone help me locate the problem and how to fix it.
The external company has no problems with loading so i can only
imagine it is our SQL Server set-up
Thanks
ArchanaHi
"archanapatelwhite@.googlemail.com" wrote:

> Hi,
> I have a DSN set-up to access a SQL Server database from another
> machine and program.
> It is basically an import manager (third party product from an
> external company), when I import it loads in a few thousand records
> and then after about 2 minutes I get the following error:
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server
does not
> exist or access denied.
> It then reconnects within a few minutes but misses loading the record
> where the error happens.
> Can someone help me locate the problem and how to fix it.
> The external company has no problems with loading so i can only
> imagine it is our SQL Server set-up
> Thanks
> Archana
>
I would initially suspect something like your Anti-virus software,
anti-intusion software or firewall is breaking the connection e.g
http://support.microsoft.com/kb/170338/en-us
You may want to also check if you are on a recent SQL Server service pack
see
http://sqlserver2000.databases.aspf...-m-running.html
John

Connection error : SQL Server does not exist or access denied

hello , i'm very new to sql server.

i'm using sql server 200 & winXP. I have a local server called "JO" and a dsn named "LocalServer".

Authentication is sql server authentication & window with username of "cying" and password "admin"

i can ping my server "JO" but cannot telnet it's sql_server_tcp_port.

this is the error code i obtained when try to open the connection:

Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.

my connection string is as below :

set objcon = server.CreateObject("ADODB.Connection")

strConnection = "Provider=MSDASQL;Driver={SQL Server};Server=local;Data Source=LocalServer;User ID=cying;Password=admin;Persist Security Info=False"

objcon.open, strConnection

is there anything wrong with my connection string or setting?

pls help, thanks in advance

ok, i can now connect to sql server but another error occurs:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'Provider=MSDASQL'.

my connection string is as below :

strConnection = "Provider=MSDASQL;Driver={SQL Server};Server=(local);User ID=cying;Password=admin;Persist Security Info=False;"

do i need to put in data source? i created the user id under enterprise manager, (local) > serucity > logins. server access is set to permit. i even try log in with sa account but still comes out the same error.

pls help, thank u

|||

Hi,

It looks like you're running into a known problem described in the BOL (http://msdn2.microsoft.com/en-us/library/ms190204.aspx):

Note:

To enhance security, SQL Server 2005 disables TCP/IP connectivity to SQL Server 2005 for new installations of the Developer, Evaluation, and SQL Server Express editions. OLEDB applications connecting with MDAC 2.8 cannot connect to the default instance on local server using ".", "(local)", or (<blank>) as the server name. To resolve this, supply the server name, or enable TCP/IP on the server. Connections to local named instances are not affected, and connections using SQL Native Client are not affected. Installations where a previous installation of SQL Server is present might not be affected.

Try changing "(local)" to the name of the server.

Il-Sung.

|||

i manage to connect to db finally after changing the code, here's my new connection string

strConn = "Provider=MSDASQL;Driver={SQL Server};server=(local);uid=cying;pwd=admin;database=cying"

II-Sung, i'm using sql server 2000 and hav TCP/IP enabled

i think the cause of problem is uid & pwd, previously i used user name & password and failed to establish a connection

thanks for ur help though =)

Connecting to SQL via ODBC : How to change the default database configured with DSN

Hello All,

Does anybody know how to achive that?

<

addname="ConnectionString"connectionString="Dsn=dsn_name;uid=user;pwd=password"providerName="System.Data.Odbc" />

I have got this created by the wizard for my sqlDataSource. I am able to get data from tables in the default database specified with the DSN.

But I want to go to other database than the default.

Thanks a lot in advamce for your help!

Kazu

DSN is tied to a particular database, which in your case is the default database. You can either a) create a new DSN and link it with the database you want to connect with or b) rewrite the connection string so that you aren't using a DSN. this link should help you write the connection stringhttp://www.sqlstrings.com/SQL-Server-connection-strings.htmhttp://www.sqlstrings.com/SQL-Server-connection-strings.htm

|||

thanks. But..

> DSN is tied to a particular database...

Not really. I have finally achived what I wanted, by simply adding "Database=" parameter to the connectionstring.

|||

> DSN is tied to a particulardefault database...

connecting to sql server from asp page

Hi,
I'm trying to establish a connection to Sql Server from an asp page using a System DSN.
I setup the System DSN to use Sql Server authentication; the userid and password
were stored with the ODBC configuration utility. Sql Server is configured to used both Windows and Sql Server authentication.
I was not able to connect using the DSN from an asp page - the exception was as follows
Message "ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login
failed for user '(null)'. Reason: Not associated with a trusted SQL Server
connection.
ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed
for user '(null)'. Reason: Not associated with a trusted SQL Server
connection.
The code snippet:
Dim connString As String = "DSN=myDSN;"
Dim conn As OdbcConnection = New OdbcConnection(connString)
Try
conn.Open()
Catch ex As Exception
End Try
Any help or article would be appreciated.
Ronny,
you'll need to complete the DNS connection string with login info. For
example:
oConn.Open "DSN=mySystemDSN;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
Also, check that SQL Server in in mixed authentication mode.HTH,Paul Ibison
|||In your DSN, if you include the login and password, then you are specifying
that the dsn should not use integrated authentication. However, the login
name and password in the login request from a DB-Library or Open Database
Connectivity (ODBC) client application are always ignored by SQL Server
(149398).
You have two choices - either supply the uid and pwd in the connection open
(as per Paul) or switch to integrated security and allocate IUSR_xxxx
appropriate permissions.
Bern
"Ronny Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:5B207EDD-6761-48CF-A924-0E3CA4756C94@.microsoft.com...
> Hi,
> I'm trying to establish a connection to Sql Server from an asp page using
a System DSN.
> I setup the System DSN to use Sql Server authentication; the userid and
password
> were stored with the ODBC configuration utility. Sql Server is configured
to used both Windows and Sql Server authentication.
> I was not able to connect using the DSN from an asp page - the exception
was as follows
> Message "ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL
Server]Login
> failed for user '(null)'. Reason: Not associated with a trusted SQL Server
> connection.
> ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed
> for user '(null)'. Reason: Not associated with a trusted SQL Server
> connection.
> The code snippet:
> Dim connString As String = "DSN=myDSN;"
> Dim conn As OdbcConnection = New OdbcConnection(connString)
> Try
> conn.Open()
> Catch ex As Exception
> End Try
> Any help or article would be appreciated.

connecting to sql server from asp page

Hi,
I'm trying to establish a connection to Sql Server from an asp page using a
System DSN.
I setup the System DSN to use Sql Server authentication; the userid and pass
word
were stored with the ODBC configuration utility. Sql Server is configured t
o used both Windows and Sql Server authentication.
I was not able to connect using the DSN from an asp page - the exception was
as follows
Message "ERROR [28000] [Microsoft][ODBC SQL Server Driver][S
QL Server]Login
failed for user '(null)'. Reason: Not associated with a trusted SQL Server
connection.
ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server
]Login failed
for user '(null)'. Reason: Not associated with a trusted SQL Server
connection.
The code snippet:
Dim connString As String = "DSN=myDSN;"
Dim conn As OdbcConnection = New OdbcConnection(connString)
Try
conn.Open()
Catch ex As Exception
End Try
Any help or article would be appreciated.Ronny,
you'll need to complete the DNS connection string with login info. For
example:
oConn.Open "DSN=mySystemDSN;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
Also, check that SQL Server in in mixed authentication mode.HTH,Paul Ibison|||In your DSN, if you include the login and password, then you are specifying
that the dsn should not use integrated authentication. However, the login
name and password in the login request from a DB-Library or Open Database
Connectivity (ODBC) client application are always ignored by SQL Server
(149398).
You have two choices - either supply the uid and pwd in the connection open
(as per Paul) or switch to integrated security and allocate IUSR_xxxx
appropriate permissions.
Bern
"Ronny Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:5B207EDD-6761-48CF-A924-0E3CA4756C94@.microsoft.com...
> Hi,
> I'm trying to establish a connection to Sql Server from an asp page using
a System DSN.
> I setup the System DSN to use Sql Server authentication; the userid and
password
> were stored with the ODBC configuration utility. Sql Server is configured
to used both Windows and Sql Server authentication.
> I was not able to connect using the DSN from an asp page - the exception
was as follows
> Message "ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL[/vb
col]
Server]Login[vbcol=seagreen]
> failed for user '(null)'. Reason: Not associated with a trusted SQL Server
> connection.
> ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Serv
er]Login failed
> for user '(null)'. Reason: Not associated with a trusted SQL Server
> connection.
> The code snippet:
> Dim connString As String = "DSN=myDSN;"
> Dim conn As OdbcConnection = New OdbcConnection(connString)
> Try
> conn.Open()
> Catch ex As Exception
> End Try
> Any help or article would be appreciated.