asked on

User cannot login to database

Hi,

or should that be Help !

i have an application written in VB using a sql server database. This all works fine on the development machine which is running sql server 2000.

when i install the application along with the database onto a test machine running MSDE i get an error stating ".....login failed..... " when the software tries to access the database.

any help would be very very welcome.

Thanks

Steve

dambuster99

ASKER

exact error is: -

cannot open database requested in login ... login fails

thanks

rafrancisco

How do you connect to the SQL Server? Are you supplying the user name and password or are you using Windows authentication?

rafrancisco

Also, make sure that the user you supplied in your connection string has access to the database specified or that the database exists.

r_a_j_e_s_h

can u pls tell me how u r creating ur DSN

Manually or programmatically,
if u r doing manually, u come to know abt connectivity while creating the DSN itself, if programmatically u should pass the SQL user name & passward correctly with respect to DB u r trying to connect

dambuster99

ASKER

Hi

the connection string is: -

If cn.State = 0 Then cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=logicstore;Data Source=."

thanks

rafrancisco

Looks like the user account used in running your application does not have access to you SQL Server database. I suggest that instead of using Integrated Security=SSPI, try passing the user name and password so that it will be consistent across your different servers.

dambuster99

ASKER

Thanks for the response,

ive changed the connect string to: -

If cn.State = 0 Then cn.Open "Provider=sqloledb;Data Source=(local);Initial Catalog=logicstore;User ID=sa;Password=;"

which does not seem to make any difference. -- i do think you are right but how do i set the database permissions for the user no matter whcih machine its installed on. The database will be local to the machine running MSDE but the user name shown on the msde is showing their login name.

Is it possible to allow access to the database regardless of login name ?

Thanks again

rafrancisco

"Is it possible to allow access to the database regardless of login name ?"

There is a way for a user to access the database no matter which machine it is installed. Just make the user an administrator of the machine where the database is installed. But this is dangerous because the user will have full control of the machine and not just SQL Server.

Also, always assign a password to the 'sa' user name and never use it in connecting to your SQL Server from an application.

Lastly, based on your error message "cannot open database requested in login", it seems that there is no "logicstore" database on the SQL Server you are trying to connect to. Were you able to verify this?

I wish I could help more.

dambuster99

ASKER

When you say that "the database is not on the server" - what do you mean ?

The database files .ldf & .mdf both sit on the client machine -

how do i tell msde that they exist ?

sorry if this is getting basic - but im stuck...

thanks again

rafrancisco

>>The database files .ldf & .mdf both sit on the client machine - how do i tell msde that they exist ? <<

You have to attach the database to MSDE. The command to attach the database is sp_attach_db. Here's an example on how to attach a database.

EXEC sp_attach_db @dbname = N'pubs',
@filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
@filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'

ASKER CERTIFIED SOLUTION

rafrancisco

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dambuster99

ASKER

Thanks for your help and Perseverance - much appreciated.

Steve