I am new to Sql Server 2005 and most probably screwed something up. I am
getting an error "The user is not associated with a trusted Sql Server
connection" when accessing my app using a regular user login.
Is NT Authority\System supposed to be both under Security/Users in MyDB and
also under the Server Security\Logins? i.e.
MyServer\DatabasesMyDatabase\Security\Us
ers\NT AUTHORITY\SYSTEM
and
MyServer\Security\Logins\NT AUTHORITY\SYSTEM
Also, is there a quick tutorial somewhere on EXACTLY how to set up users and
their rights?
Any help will be greatly appreciated! I am supposed to have this out
tomorrow --
YIKES!!
--
SandyThis normally when sql server is not able to authentic the connection to a
domain account. This can happen when the user connecting to sql server has
not logged there machine onto the domain prior to connecting to the domain
where SQL Server lives. Or you are trying to connect to SQL Server via an
application that lives on a different machine then the user, and the databas
e
server, so you your command is taking a double hoop to get to sql server.
Have you tried to connect via SQL Server authentication? "Sandy" wrote:
> Hello -
> I am new to Sql Server 2005 and most probably screwed something up. I am
> getting an error "The user is not associated with a trusted Sql Server
> connection" when accessing my app using a regular user login.
> Is NT Authority\System supposed to be both under Security/Users in MyDB an
d
> also under the Server Security\Logins? i.e.
> MyServer\DatabasesMyDatabase\Security\Us
ers\NT AUTHORITY\SYSTEM
> and
> MyServer\Security\Logins\NT AUTHORITY\SYSTEM
> Also, is there a quick tutorial somewhere on EXACTLY how to set up users a
nd
> their rights?
> Any help will be greatly appreciated! I am supposed to have this out
> tomorrow --
> YIKES!!
> --
> Sandy|||Hi Greg -
Thanks so much for your response!
I am at home now and don't have access to the database or Sql Server at work
presently, but from my recollection, I have the NT Authority\System as the
login for both the Server and the database. I think that's wrong and I
should probably delete the NT Authority\System from the database part.
At any rate, the application resides on a server on the K drive. All users
are members of AAA\CustomerService in the Active Directory. Sql Server
resides on Machine DAO401235. All users obviously have their own machines.
I thought this would be simple -- just Windows authentication, then set the
role so that Windows user account can have access to all the stored
procedures and are denied access to the tables. I also thought it would be
something that could be easily done through the GUI and not code.
I have purchased several books since this post (yes, just a couple of hours
ago -- that's how frantic I am) and all of them are oriented towards setting
Sql Server logins, instead of using Windows Authority. I would suspect they
don't even cover the Windows stuff because it's too simplistic. I can't,
however figure it out myself and apparently need it "spoon fed."
Can you give me clues on how to set this up?
Thanks again for your reply and please stay with me on this, if you can!
--
Sandy
"Greg Larsen" wrote:
> This normally when sql server is not able to authentic the connection to a
> domain account. This can happen when the user connecting to sql server h
as
> not logged there machine onto the domain prior to connecting to the domain
> where SQL Server lives. Or you are trying to connect to SQL Server via an
> application that lives on a different machine then the user, and the datab
ase
> server, so you your command is taking a double hoop to get to sql server.
> Have you tried to connect via SQL Server authentication? "Sandy" wrote:
>|||Sandy,
After the user is added to SQL add the user to the database in which
they need access. Check to make sure that the default database for the user
is set to the database for the application. Once given rights to log into
SQL the user will then go to their default database or the database within
the connection string. Authentication fails if the user does not have right
s
within the specified database.
"Sandy" wrote:
> Hi Greg -
> Thanks so much for your response!
> I am at home now and don't have access to the database or Sql Server at wo
rk
> presently, but from my recollection, I have the NT Authority\System as the
> login for both the Server and the database. I think that's wrong and I
> should probably delete the NT Authority\System from the database part.
> At any rate, the application resides on a server on the K drive. All user
s
> are members of AAA\CustomerService in the Active Directory. Sql Server
> resides on Machine DAO401235. All users obviously have their own machines
.
> I thought this would be simple -- just Windows authentication, then set th
e
> role so that Windows user account can have access to all the stored
> procedures and are denied access to the tables. I also thought it would b
e
> something that could be easily done through the GUI and not code.
> I have purchased several books since this post (yes, just a couple of hour
s
> ago -- that's how frantic I am) and all of them are oriented towards setti
ng
> Sql Server logins, instead of using Windows Authority. I would suspect th
ey
> don't even cover the Windows stuff because it's too simplistic. I can't,
> however figure it out myself and apparently need it "spoon fed."
> Can you give me clues on how to set this up?
> Thanks again for your reply and please stay with me on this, if you can!
> --
> Sandy
>
> "Greg Larsen" wrote:
>|||What exactly do you mean by when you say "I have the NT Authority\System as
the login for both the Server and the database"? The "NT Authority\System"
account known as the "Local System" account, or the Local "Administrator"
account on a computer. This account can only be used to access resource
locally on the machine, and not network resources. So basically this is not
an account any user might connect to your database with. The "NT
Authority\System" account might only be used if you where to login to your
sql server machine locally as "Administrator".
In order for your users to connect using windows authentication, you would
need to add the users domain\user account to sql server. Or grant a windows
group in which the users reside to sql server. The users would then also
need to be given permissions to the database for which you want to give them
access.
"Sandy" wrote:
> Hi Greg -
> Thanks so much for your response!
> I am at home now and don't have access to the database or Sql Server at wo
rk
> presently, but from my recollection, I have the NT Authority\System as the
> login for both the Server and the database. I think that's wrong and I
> should probably delete the NT Authority\System from the database part.
> At any rate, the application resides on a server on the K drive. All user
s
> are members of AAA\CustomerService in the Active Directory. Sql Server
> resides on Machine DAO401235. All users obviously have their own machines
.
> I thought this would be simple -- just Windows authentication, then set th
e
> role so that Windows user account can have access to all the stored
> procedures and are denied access to the tables. I also thought it would b
e
> something that could be easily done through the GUI and not code.
> I have purchased several books since this post (yes, just a couple of hour
s
> ago -- that's how frantic I am) and all of them are oriented towards setti
ng
> Sql Server logins, instead of using Windows Authority. I would suspect th
ey
> don't even cover the Windows stuff because it's too simplistic. I can't,
> however figure it out myself and apparently need it "spoon fed."
> Can you give me clues on how to set this up?
> Thanks again for your reply and please stay with me on this, if you can!
> --
> Sandy
>
> "Greg Larsen" wrote:
>|||Thanks for your response, Derekman!
> After the user is added to SQL add the user to the database in which
> they need access.
"After the user is added to SQL" - Do you mean something like:
Create Login [AAA\jcpracti] from Windows;
"add the user to the database in which they need access" - Do you mean:
Use MyDatabase
Create User [AAA\jcpracti]
> Check to make sure that the default database for the user
> is set to the database for the application.
How do I do this?
Also, I am puzzled by the fact that NT Authority\System keeps being put unde
r
MyDatabase\Security\Users\NT AUTHORITY\SYSTEM when I create a new user. I
didn't think it belonged here, but Sql Server keeps throwing it in.
It's already under the Security folder for the Sql Server Instance.
Any help will be greatly appreciated!
Sandy
"Derekman" wrote:
> Sandy,
> After the user is added to SQL add the user to the database in which
> they need access. Check to make sure that the default database for the us
er
> is set to the database for the application. Once given rights to log into
> SQL the user will then go to their default database or the database within
> the connection string. Authentication fails if the user does not have rig
hts
> within the specified database.
> "Sandy" wrote:
>|||Hi -
I misspoke - I meant to refer to the folders on the left in Management
Studio --
Under the Server\Security\Logins is an icon that says NT AUTHORITY\SYSTEM an
d
also under MyDatabase\Security\Users is another icon that says NT
AUTHORITY\SYSTEM. Should both of these be there?
>In order for your users to connect using windows authentication, you would
>need to add the users domain\user account to sql server. Or grant a window
s
>group in which the users reside to sql server.
Where and how would I do this? Also, what do you mean by "...add....TO SQL
SERVER" and "...grant...TO SQL SERVER? That brings up connotations to me of
using Sql Server authentication.
I am sooooo completely
with the language used in this security thing!
Sandy
"Greg Larsen" wrote:
> What exactly do you mean by when you say "I have the NT Authority\System a
s
> the login for both the Server and the database"? The "NT Authority\System
"
> account known as the "Local System" account, or the Local "Administrator"
> account on a computer. This account can only be used to access resource
> locally on the machine, and not network resources. So basically this is n
ot
> an account any user might connect to your database with. The "NT
> Authority\System" account might only be used if you where to login to your
> sql server machine locally as "Administrator".
> In order for your users to connect using windows authentication, you would
> need to add the users domain\user account to sql server. Or grant a windo
ws
> group in which the users reside to sql server. The users would then also
> need to be given permissions to the database for which you want to give th
em
> access.
> "Sandy" wrote:
>|||You can add user in Management Studio under the "Security\Login
folder. To do this right click on the Logins folder and select "New
Login..." in the "New Login" page enter the <domain>\<user> in the "Login
Name" field with the "Windows Authentication" button set. Then under "User
Mappings" give them access to the correct database and role. Hope this make
sense.
For SQL Server examples check out my website at:
http://www.geocities.com/sqlserverexamples
"Sandy" wrote:
> Hi -
> I misspoke - I meant to refer to the folders on the left in Management
> Studio --
> Under the Server\Security\Logins is an icon that says NT AUTHORITY\SYSTEM
and
> also under MyDatabase\Security\Users is another icon that says NT
> AUTHORITY\SYSTEM. Should both of these be there?
>
> Where and how would I do this? Also, what do you mean by "...add....TO S
QL
> SERVER" and "...grant...TO SQL SERVER? That brings up connotations to me
of
> using Sql Server authentication.
> I am sooooo completely
with the language used in this security thing!
> --
> Sandy
>
> "Greg Larsen" wrote:
>|||Thanks, Greg.
I tried it and I'm still getting the message
Login failed for user ". The user is not associated with
a trusted SQL Server connection.
BTW, isn't the user name normally in the error message?
At any rate, I'm at my wits end and probably at the end of this job (as in
fired). Any other ideas about what could be causing this?
Thanks again for your patience!
Sandy
"Greg Larsen" wrote:
> You can add user in Management Studio under the "Security\Login
> folder. To do this right click on the Logins folder and select "New
> Login..." in the "New Login" page enter the <domain>\<user> in the "Login
> Name" field with the "Windows Authentication" button set. Then under "Us
er
> Mappings" give them access to the correct database and role. Hope this ma
ke
> sense.
> --
> For SQL Server examples check out my website at:
> http://www.geocities.com/sqlserverexamples
> "Sandy" wrote:
>|||Sandy,
Follow Greg's instructions and that should fix it. You can also use
T-SQL to do the same task, but this way is definitely easier.
You had also asked about training. the below link takes you to on
demand web casts. Kimberly Tripp recently completed an 11 part web series,
TechNet Webcast: SQL Server 2005 for the IT Professional, that would
definitely help along with numerous other webcasts that are available.
You can also go to http://www.microsoft.com/sql/eval/e-learn.mspx which will
provide more free courses from Microsofton SQL 2005.
Good Luck
"Greg Larsen" wrote:
> You can add user in Management Studio under the "Security\Login
> folder. To do this right click on the Logins folder and select "New
> Login..." in the "New Login" page enter the <domain>\<user> in the "Login
> Name" field with the "Windows Authentication" button set. Then under "Us
er
> Mappings" give them access to the correct database and role. Hope this ma
ke
> sense.
> --
> For SQL Server examples check out my website at:
> http://www.geocities.com/sqlserverexamples
> "Sandy" wrote:
>sql
