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
PZTo 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...
> 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
>>|||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...
> > 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
> >>
> >>
> >>
>
>sql