Authentication problem on 2005 Express Edition

I'll start from the beginning: I have a Windows application developed in C#.
It uses a SQL Server 2005 Express Edition database to store data. Now I have
created a deployment of this app and database, and I try to install it on my
own machine. No problems, works without errors.
Now, I put this deployment project on the network and a user downloads it to
his Vista machine and runs the setup. When he starts the app he gets the
following error message:
"Login failed for user "MyUser". The user is not associated with a thrusted
server connection".
The connection string I use in my application is this:
Server=.\SQLEXPRESS;Database=MyDB;User ID=MyUser;Password=thissucks
In the SQL Server Management Studio Express I have already marked the "SQL
Server and Windows authentication mode" (mixed mode?) under
server/options/security. If
there is anywhere else this should be set, please let me know.
Since I have no control over the Vista machine, I can't force him/her to
create a new account that is thrusted. All I want is to deploy my app and
database, without having to worry about authorization and any other weird
stuff, since in many cases I don't know the setup on each box.
I have already tried alot of the suggestions I've found, but let's start all
over so it will be right. What should I do to remove this error? How can I
deploy my database without having to think about authorization/security
stuff?
TIA
PZ
To resolve your issue, tell me how you defined the User MyUser at MyDB level;
is it mapped to a login? In this case, the login is a Windows login or a SQL
login?
If it is a Windows login (...CREATE LOGIN loginname FROM WINDOWS...) it
should map a Domain Account, otherwise on the new machine it will be not
authenticated, and yolu should script a new login and remap the MyUser to
this new login.
Gilberto Zampatti
> I'll start from the beginning: I have a Windows application developed in C#.
> It uses a SQL Server 2005 Express Edition database to store data. Now I have
> created a deployment of this app and database, and I try to install it on my
> own machine. No problems, works without errors.
> Now, I put this deployment project on the network and a user downloads it to
> his Vista machine and runs the setup. When he starts the app he gets the
> following error message:
> "Login failed for user "MyUser". The user is not associated with a thrusted
> server connection".
> The connection string I use in my application is this:
> Server=.\SQLEXPRESS;Database=MyDB;User ID=MyUser;Password=thissucks
> In the SQL Server Management Studio Express I have already marked the "SQL
> Server and Windows authentication mode" (mixed mode?) under
> server/options/security. If
> there is anywhere else this should be set, please let me know.
> Since I have no control over the Vista machine, I can't force him/her to
> create a new account that is thrusted. All I want is to deploy my app and
> database, without having to worry about authorization and any other weird
> stuff, since in many cases I don't know the setup on each box.
> I have already tried alot of the suggestions I've found, but let's start all
> over so it will be right. What should I do to remove this error? How can I
> deploy my database without having to think about authorization/security
> stuff?
> TIA
> PZ
>
>
|||Hi Gilberto
Sounds reasonable. I'll try to describe it here, so you can determine if I
am missing something (please note that I use Server Management Studio
Express on my development machine to deal with these issues, while I don't
setup anything on the deployment machine and I run no scripts):
- First of all, I want to create everything on database level/scope, so when
the database is deployed the user installing it don't have to create/change
any user/authenticaion in Windows at all.
- I have created a new User account in the database "MyDB" scope (under
"SQLExpress\databases\MyDB\Security\Users". This user is called "MyUser"
- This user is given 2 role memberships: db_datareader and db_datawriter
- This user is not set to any schema ownerships and no extended properties
are added
- Under the "SQLExpress\databases\MyDB\properties" I have added the user
"MyUser" as type "User" and given him the following explicit permissions:
Connect, Insert, Update, Delete, Execute
I have also created this user under "SQLExpress\Security\Logins" and
selected the option "SQL Server authentication" without selecting "Enforce
password policy". However, I fail to se if this is required, since this is
not on database level, but on server level. And I only deploy the database
files. Could it be that I'm missing this user on the other server under
"Logins"? Is that why I need a script to create it?
Can you explain to me how the database is deployed on the other machine?
Does it also create a server object and store the database MyDB within? In
that case, it could be true that I need to run some scripts to put that new
server object up-to-date?
Please advise.
Regards,
PZ
"Gilberto Zampatti" <GilbertoZampatti@.discussions.microsoft.com> wrote in
message news:EF361E5F-D149-4006-8E5E-C0001E03E511@.microsoft.com...[vbcol=seagreen]
> To resolve your issue, tell me how you defined the User MyUser at MyDB
> level;
> is it mapped to a login? In this case, the login is a Windows login or a
> SQL
> login?
> If it is a Windows login (...CREATE LOGIN loginname FROM WINDOWS...) it
> should map a Domain Account, otherwise on the new machine it will be not
> authenticated, and yolu should script a new login and remap the MyUser to
> this new login.
> Gilberto Zampatti
|||What do you mean when you write "...And I only deploy the database
> files...": did you bak-up and restore the dbfiles or detach-attache the same files or what else?
It seems to me that in the deployed instance you are not able to mat MyUser
to a valid login (remember that this mapping is made considering some "sid"
of the login and not simply the login name.
The simplest way to operate could be:
- deploy (once) an instance of SQL Express and on each instance run a script
to create a SQL login (why not a Windows login?) named xxx
- deploy your DB files
- launch a script to:
- drop MyUser
- create MyUser mapping it to the xxx login and grant him the needed
permissions
Gilberto Zampatti
"Preben Zacho" wrote:

> Hi Gilberto
> Sounds reasonable. I'll try to describe it here, so you can determine if I
> am missing something (please note that I use Server Management Studio
> Express on my development machine to deal with these issues, while I don't
> setup anything on the deployment machine and I run no scripts):
> - First of all, I want to create everything on database level/scope, so when
> the database is deployed the user installing it don't have to create/change
> any user/authenticaion in Windows at all.
> - I have created a new User account in the database "MyDB" scope (under
> "SQLExpress\databases\MyDB\Security\Users". This user is called "MyUser"
> - This user is given 2 role memberships: db_datareader and db_datawriter
> - This user is not set to any schema ownerships and no extended properties
> are added
> - Under the "SQLExpress\databases\MyDB\properties" I have added the user
> "MyUser" as type "User" and given him the following explicit permissions:
> Connect, Insert, Update, Delete, Execute
> I have also created this user under "SQLExpress\Security\Logins" and
> selected the option "SQL Server authentication" without selecting "Enforce
> password policy". However, I fail to se if this is required, since this is
> not on database level, but on server level. And I only deploy the database
> files. Could it be that I'm missing this user on the other server under
> "Logins"? Is that why I need a script to create it?
> Can you explain to me how the database is deployed on the other machine?
> Does it also create a server object and store the database MyDB within? In
> that case, it could be true that I need to run some scripts to put that new
> server object up-to-date?
> Please advise.
> Regards,
> PZ
> "Gilberto Zampatti" <GilbertoZampatti@.discussions.microsoft.com> wrote in
> message news:EF361E5F-D149-4006-8E5E-C0001E03E511@.microsoft.com...
>
>