SQL error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Snowman - 11 Mar 2008 14:05 GMT

Hi group

hope that you can help me...

Created a backup of a database from our Production server (x64 bit) and
moved the backup file to our develop (32 bit) server and restored it as the
same database name.

Now from the application that uses the DB - I can't login to that database -
getting a 18456 error -

the application that used the db in the prod. are configured with a specific
<username> - the application on the development server are configured with
the same <username> - both username accounts are local users (not AD user)
both servers run mixed mode. Have tried to create a new user to
access...without luck

anyone ?

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 11 Mar 2008 14:35 GMT

> Now from the application that uses the DB - I can't login to that
> database - getting a 18456 error -

Can you be more specific? What is the TEXT of the error message (I'm not
going to speak for everyone else, but I haven't memorized all of the error
codes)? Are you using SQL authentication or Windows authentication? Have
you checked to make sure there is a valid user in the database, and that it
is associated with a valid login?

A

Reply to this Message

Snowman - 11 Mar 2008 14:46 GMT

Yes sure...

Connection failed:
SQLState: '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Driver][SQL Server]Login failed for user 'user_name'.
Connection failed:
SQLState:'01S00'
SQL Server Error: 0
[Microsoft][ODBC SQL Server Driver]Invalid connection string attribute

using mixed login both SQL authentication/Windows authentication

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 11 Mar 2008 14:50 GMT

Well, the app is either using SQL or Windows authentication, it can't be
using both at the same time. Right now it looks like it is attempting to
connect using SQL authentication. What happens when you forget the app for
a minute, and attempt to connect with Management Studio, from the same box,
using the same credentials?

> Yes sure...
>
[quoted text clipped - 7 lines]
>
> using mixed login both SQL authentication/Windows authentication

Reply to this Message

Zarko Jovanovic - 11 Mar 2008 14:51 GMT

> Hi group
>
[quoted text clipped - 14 lines]
>
> anyone ?

orphaned users problem, check sp_change_users_login

Reply to this Message

Snowman - 12 Mar 2008 09:42 GMT

Hi guys

Thanks four your reply - I'll look into the check sp_change_users_login

The weirded part of it...that even if I create a new empty Db and point the
App to that (it should then create a schema) I get and SQL error verifying
rights.

"Using a SQL Login to access the database requires that this login have DBO
rights because it is used by this application to configure the database.
Using trusted Authentification to acces the database only requires that the
previously defined MSCMS system account have the following database
privileges: db_datareader, db_datawriter and db_ddladmin."

The user I defined have all the mentioned rights...

>> Hi group
>>
[quoted text clipped - 15 lines]
>> anyone ?
> orphaned users problem, check sp_change_users_login

Reply to this Message