Login failed for user

Hi All
I am using MSDE2K and an application written in VB6
After installing MSDE and my program on to a new computer I am having
strange problems when saving data which I didn't get on the old computer
My connection string is...
cn.ConnectionString = "Provider=SQLOLEDB;Persist Security Info=False;User
ID=Tramcars;Password=ga711bm;Initial Catalog=Tramcars;Data Source=(local)"
(User ID Tramcars exists in MSDE and is included in the db_owner role)
This connects me OK and allows me to enter new data and save
On creating a new entry and attempting to save again I get the message
'Login failed for user Tramcars'
If I set 'Persist Security Info=True' in the connection string, all works
fine
I am completely lost
Any ideas?
Regards
Steve
hi Steve,
steve wrote:
> Hi All
> I am using MSDE2K and an application written in VB6
> After installing MSDE and my program on to a new computer I am having
> strange problems when saving data which I didn't get on the old
> computer
> My connection string is...
> cn.ConnectionString = "Provider=SQLOLEDB;Persist Security
> Info=False;User ID=Tramcars;Password=ga711bm;Initial
> Catalog=Tramcars;Data Source=(local)"
> (User ID Tramcars exists in MSDE and is included in the db_owner role)
> This connects me OK and allows me to enter new data and save
> On creating a new entry and attempting to save again I get the message
> 'Login failed for user Tramcars'
> If I set 'Persist Security Info=True' in the connection string, all
> works fine
>
MSDE installs by default disabling standard SQL Server authenticated
connections..
thus only WinNT (trusted) connections will work..
you can modify this behavior both at install time, providing the
SECURITYMODE=SQL
parameter to the setup.exe boostrap installer, or later, at "run-time",
hacking the windows registry as described in
http://support.microsoft.com/default...b;en-us;285097
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea
MSDE is set to mixed mode when this happens
Strange thing is it allows me to save once, but fails with message on second
save?
Regards
Steve
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3n8lalFcjubU1@.individual.net...
> hi Steve,
> steve wrote:
> MSDE installs by default disabling standard SQL Server authenticated
> connections..
> thus only WinNT (trusted) connections will work..
> you can modify this behavior both at install time, providing the
> SECURITYMODE=SQL
> parameter to the setup.exe boostrap installer, or later, at "run-time",
> hacking the windows registry as described in
> http://support.microsoft.com/default...b;en-us;285097
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Steve,
steve wrote:
> Hi Andrea
> MSDE is set to mixed mode when this happens
> Strange thing is it allows me to save once, but fails with message on
> second save?
>
you mean you can connect with the provided connection string once, but if
you try a second time you get that exception?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea
Yes thats exactly what I mean
I can connect to msde when my program starts, to populate comboboxes etc
I always close the connection when not needed (con.close)
I can then create 1 invoice and save it OK, which requires..
con.open
code to save data....
con.close
If I then try to save another invoice
con.open
etc
con.close
Error raised at con.open (login failed for user Tramcarslogin)
It is like the connectionstring forgets the login password on the second
attempt
If I use persist security info=true in the connectionstring I don't get the
error and everything is OK
Regards
Steve
"Andrea Montanari" wrote:

> hi Steve,
> steve wrote:
> you mean you can connect with the provided connection string once, but if
> you try a second time you get that exception?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||hi Steve,
Steve wrote:
> Hi Andrea
> Yes thats exactly what I mean
> I can connect to msde when my program starts, to populate comboboxes
> etc
> I always close the connection when not needed (con.close)
> I can then create 1 invoice and save it OK, which requires..
> con.open
> code to save data....
> con.close
> If I then try to save another invoice
> con.open
> etc
> con.close
> Error raised at con.open (login failed for user Tramcarslogin)
> It is like the connectionstring forgets the login password on the
> second attempt
> If I use persist security info=true in the connectionstring I don't
> get the error and everything is OK
ok, I think I understand... or hope so...
you do something like
Dim sCon As String
Dim oCon As ADODB.Connection
Set oCon = New ADODB.Connection
sCon = "Provider=sqloledb;Data Source=(Local);Initial Catalog=pubs;User
Id=sa;Password=xxx;persist security info=false"
oCon.Open sCon
DoSomethingWithConnection oCon ' here you consume a recordset or command or
the like
oCon.Close
oCon.Open
DoSomethingWithConnection oCon ' here you consume a recordset or command or
the like
oCon.Close
Set oCon = Nothing
that's to say you open the connection the second time without providing a
connection string and relying on the old provided one..
the specific property, "Persist Security Info" is there to specify whether
or not the data source object is allowed to persist sensitive authentication
information (such as pwd) along with other info.. as it has been set to
false, there's no way for the 2nd open method to know such a kind of info,
and the provider will refuse to pass the connection on..
if this is the case, you should not rely on such a mechanism for 2 reason..
1st you always should completely destroy the ADODB.Connection object when
not needed,
oCon.Close
Set oCon = Nothing
so that all finalizers of the object will be properly called, and the conn
will return to the connection pool...
2nd, (and also becouse of the preceding one) you always should provide a
full qualified connection string...
is a comparable/compatible one can be found in the connection pool, it will
be picked up from there, or a new one will be createad ...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea
Thanks again for a great explanation
Explains everything
Regards
Steve
"Andrea Montanari" wrote:

> hi Steve,
> Steve wrote:
> ok, I think I understand... or hope so...
> you do something like
> Dim sCon As String
> Dim oCon As ADODB.Connection
> Set oCon = New ADODB.Connection
> sCon = "Provider=sqloledb;Data Source=(Local);Initial Catalog=pubs;User
> Id=sa;Password=xxx;persist security info=false"
> oCon.Open sCon
> DoSomethingWithConnection oCon ' here you consume a recordset or command or
> the like
> oCon.Close
> oCon.Open
> DoSomethingWithConnection oCon ' here you consume a recordset or command or
> the like
> oCon.Close
> Set oCon = Nothing
> that's to say you open the connection the second time without providing a
> connection string and relying on the old provided one..
> the specific property, "Persist Security Info" is there to specify whether
> or not the data source object is allowed to persist sensitive authentication
> information (such as pwd) along with other info.. as it has been set to
> false, there's no way for the 2nd open method to know such a kind of info,
> and the provider will refuse to pass the connection on..
> if this is the case, you should not rely on such a mechanism for 2 reason..
> 1st you always should completely destroy the ADODB.Connection object when
> not needed,
> oCon.Close
> Set oCon = Nothing
> so that all finalizers of the object will be properly called, and the conn
> will return to the connection pool...
> 2nd, (and also becouse of the preceding one) you always should provide a
> full qualified connection string...
> is a comparable/compatible one can be found in the connection pool, it will
> be picked up from there, or a new one will be createad ...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>