Re: Parameterized query problem

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Further to below

I had a typo in the password in the ASP script so I am now back to the
originial error of

Microsoft OLE DB Provider for SQL Server error '80004005'
Cannot open database "Mydb" requested by the login. The login failed.

I tried creating a DSN and using test connection. this worked (that how I
found the typo)

I can even login to SSMS using the aspuser and open the tabale in mydb and
view it (other dbs are not accessable as I would expect)

but this code still returns the above error

<%
Dim conPubs
Set conPubs = Server.CreateObject("ADODB.Connection")
strConn = "Provider=SQLOLEDB; Data Source=127.0.0.1; Initial Catalog=mydb;
User Id=aspuser; Password=xxxx;"
conPubs.Open strConn
If ConPubs.State = adStateOpen Then
Response.Write "Connection to " & conPubs.DefaultDatabase & " created
succeassfully"
else
Response.Write "Connection failed"
end if
conPubs.Close
Set conPubs = Nothing
%>

I used

EXEC sp_helplogins 'aspuser'

and the default database does appear to be mydb

Thanks in advance




"MiniEggs" <b@xxxxx> wrote in message
news:uRjaJOacKHA.4884@xxxxxxxxxxxxxxxxxxxxxxx
Thanks Bob

Point taken about paramerterized queries. I will look into this now

Just been back and checked the server here

Yes there is a user called aspuser in the Database>Security>Login that I
setup

In the Database>Mydb>Security>Users aspuser shows as being
db_owner,db_reader,db_writer

The server is set to use Windows and SQL security

The connection string looks like this

Provider=SQLOLEDB; Data Source=127.0.0.1; Initial Catalog=Mydb; User
Id=aspuser;Password=xxxx

The error I get back is this

Microsoft OLE DB Provider for SQL Server error '80040e4d'
Login failed for user 'aspuser'.

Thanks


"Bob Barrows" <reb01501@xxxxxxxxxxxxxxx> wrote in message
news:OqGrSC5bKHA.4724@xxxxxxxxxxxxxxxxxxxxxxx
MiniEggs wrote:
Bob

I'll be in touch again next week if I can't get this (?) to work

The reason I started down the @P1 path was beacuse of the Classic ASP
section of this article

http://www.owasp.org/index.php/Reviewing_Code_for_SQL_Injection

As long as you're using parameters, sql injection is impossible. The
method for passing values to those parameters is irrelevant, so you might
as well use the easiest technique.


With regards to the ....

Give us more details if you want help with this.
Did you use SSMS to configure your SQL Server to use mixed
uthentication? -YES
Did you then create a SQL Login? -not sure

Why not? It's simply a matter of going to the Security tab in SSMS for
your server and creating a new login that is not mapped to a Windows
login. Call it ASPlogin and assign it a password.

Did you then create a user based on that login in your database? -YES

How can you answer "YES" if you're not sure of the previous answer?

(in that I created a user and ticked the box with the db name in it)

I'm sure I did someting wrong in the setting up of the user

I'm surprised I can't find any examples with screen shots on how to
do this (specifically for IIS/ASP login) as there must have been
millions who've done it .... but as I say I must be doing something
obviously wrong


Don't you have SQL Books Online (BOL)?? There are zillions of examples in
there (OK, maybe one or two ... )

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"





.

Relevant Pages

  • Re: Permission question - another one
    ... If I add an Sql Login it does add the TRAVAC\ in front of the names, ... seems to be users that were setup to use SQL Server Authentication. ... RAPTOR is the Server that has SQL Server running on it. ... > " I could think I am taking permissions away from someone, ...
    (microsoft.public.sqlserver.programming)
  • Re: Renamed Windows login not found in SQL Server 2000
    ... It's almost like SQL tuck some knowledge away in an area ... of memory that only gets released on Windows stop. ... > I am running SQL Server 2000 SP2 with Windows ... > login gets corrupted) I am unable to add the new login to ...
    (microsoft.public.sqlserver.security)
  • Re: dsn-less connection
    ... It worked pretty much as I was hoping in prompting the user for a SQL ... Server login and was able to remove the specific dsn that's in the odbc ... straight to the SQL Server login prompt? ... with the login prompt but the Use Trusted Connection is checked on. ...
    (microsoft.public.access.security)
  • Logging in irrespective of the database access settings
    ... My problem is that in the Login section of Enterprise Manger I have to ... like there used to be in SQL 6.5. ... What's the point in having the Database Access section if the System Admin ... SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: Logging in irrespective of database access
    ... My problem is that in the Login section of Enterprise Manger I have to ... like there used to be in SQL 6.5. ... What's the point in having the Database Access section if the System Admin ... SQL Server MVP ...
    (microsoft.public.sqlserver.programming)