(CROSS POSTED) Very strange: Connection under process info not whats in front-end

[Mark]

Firstly... Sorry for crossposting (microsoft.public.sqlserver.security) but
I'm desperate and confused (

I'm using SQL 2000 on XP with an ADP front-end.

I just set up a test database so I can start figuring out security etc.

The front-end connection is using Windows authentication and is logged on as
myself (Mark) yet when I look at process info in current activity in
Enterprise Manager the user shown is the one logged onto that machine (i.e.
the server) not the one I'm connecting with. When I do the same on the live
network I can see the actual users userID.

Anyone know whats going on or can point me in the right direction to sorting
it out?

I am trying to set and test permissions but on the test network I always
seem to be connecting as the server administrator.

Thanks very much

Mark

 

[aaron.kempf]

do you have this setup as linked servers?

do you allow double-hop authentication?

 

[aaron.kempf]

more importantly you can do SUSER_SNAME() and that will give you the
real username.

give us a billion times more technical explanation of what you're doing

 

[Mark]

Hi... Aaron

To both questions the answers no.

I believe I found the answer (i.e. it was all my fault). I was running the
server and client on my own test network which does not bother with a domain
controller. suser_sname was always returning the name of the currently
logged in user on the server. Would this explain the problem?

Many thanks

Mark

 

[aaron.kempf]

yeah it sure could have an effect.

you mean you are allowing guest connections or something?

for sure i would join the db server to the domain; unless you have an
obvious reason not to do that

hth

-Aaron

 

[Mark]

My reason for not joining the domain... It's embarassing to admit but... I
have 3 PC's all next to me (my test network, which is completely isolated),
the noise drives me crazy. I can hear it in my sleep. The (domain) server is
the loudest and unless I really need it I keep it switched off. I think
maybe for testing security I will set up a test database on the live
network.

I also think I need to do some serious reading.

Thanks again

Mark

 

[aaron.kempf]

do you mean that you're getting 'dbo' as the username?

tell us more about the symtoms.. are you seeing this in profiler??

you also could do HOST_NAME() in order to get the name of the pc you're
connecting from.. right?

-Aaron

ps - my dc is my loudest machine also

 

[Mark]

Hi Aaron...

suser_sname is returning the name of the server (as opposed to the client)
and the name of the current login on the server (as opposed to the current
login on the client). HOST_NAME() returns the name of the client machine as
expected. The "User" property of the relevant processes in EM's current
activity section show the same as suser_sname.

I stopped trying to sort it out as it's only a test set-up and I'm up to my
neck in it at the moment. Things seem to be behaving ok on the live network.

I don't fancy firing up the server and reconnecting to the domain as it
turned into a real time waster last time. After leaving the domain, winding
down the server and reverting to "Workgroup" (rebooting etc.), I could no
longer use remote desktop. That took me a couple of hours to sort out....
The phrase of the day was "the technollogy is revolting". I'll have another
stab at in a couple of days )

Cheers

Mark
PS - Never take a wooden cabinet, cut out a slot in the back and use it to
disguise a computer. The whole thing becomes a speaker cabinet. It's like
having a fan in my ear ;o(

 

[amous mohamed]

ok are you ready to spek with me

Hi... Aaron

To both questions the answers no.

I believe I found the answer (i.e. it was all my fault). I was running the
server and client on my own test network which does not bother with a
domain controller. suser_sname was always returning the name of the
currently logged in user on the server. Would this explain the problem?

Many thanks

Mark