SQLDMO can login username but windows can't

[kenzai]

Hello guys i'm just wondering how I have done this....I don't think this is an accident..But anyone explain me why does this situation occurs...?

I have developed an application like the SQL SERVER Service Manager. My application uses the SQLDMO under C#. I am logging to sql servers using windows authentication.

The odd thing is this: WHEN I USED THE NATIVE SQL SERVICE MANAGER, IT WILL PROMPT ME THAT I DON'T HAVE ACCESS BUT WHEN I AM USING MY APPLICATION WITH WINDOWS AUTHENTICATION, I CAN STOP ANY SERVER, BUT APPARENTLY I CAN'T START IT ANYMORE... THE ACCOUNT I AM USING HAS NO PRIVILEGED IN STOPPING THE SERVER....CAN ANYBODY EXPLAIN ME WHY THAT HAPPENED....

Succeess is never final and failure is never fatal. It's Courage that counts.

 

[NoCoolHandle]

Interesting...

What client librarys are you using?
and
what server librarys are you using?

I have seen some bizzare changes as to how naming can effect how and if you can connect.

For instance, using the . or (local) to connect to your pc works in NamedPipes but not TCP/IP and Using an IP address and Port works in TCP/IP but wont in NamedPipes.

Is the ServerName the same in servicemanager and also SQLDmo?



HTH


Rob

 

[kenzai]

Yeah the the names of the server are the same in sqlservicemanager and my sqldmo application.... I just used the windows authentication to login to sqlserver. But the odd thing is that the account i am trying to log in has no priviledges on controlling the server....... Do you have an idea why that happened..... I am wondering if this is a bug for the sql Server.... do you know how the sqlservicemanager authenticate the user.. if i am not mistaken...it is using the same authentication like the sqldmo.....when it uses the windows authentication

Succeess is never final and failure is never fatal. It's Courage that counts.

 

[NoCoolHandle]

Kenzai, I will run some tests tomorrow and see if I can figure out what the authentication differences are..

In the mean time however... What client and server connections librarys does your server support.. (named pipes/tcpip - defaults) is it your local server or are you traveling accross the network. And if you are traveling accross the network, is your dev system in the same domain (do you authenticate with the domain when you login)

From memory "SQL SERVER TOOLS" use ODBC to connect (definintly the case with EM and QA, but I am not certain of the Service manager.

I will get back after running some tests... probably 16 hrs from this post..


Rob

 

[kenzai]

Hey rob thanks, by the way I run some test with how the DMO Login,

When I am using the .LoginSecure = True, and i set up a dialog box wherein it will catch what user the dmo is using...it uses the sa account...but the odd thing is that I am using the windows authentication which as expected the dmo will used the account I logged in to windows for the sqlserver login......

Succeess is never final and failure is never fatal. It's Courage that counts.

 

[kenzai]

I AM SORRY, I AM WRONG WITH MY LAST POST...IT DOES NOT USE THE SA ACCOUNT...... TRY TO WAIT FOR YOUR TEST..... THANKS I REALLY APPRECIATE IT..

Succeess is never final and failure is never fatal. It's Courage that counts.

 

[kenzai]

Please include in your test..... if you can stop a server using an account with no priviledges in stopping it...and why does i can connect to any sql server but when i am using sqlservicemanager i cannot.... I REALLY APPRECIATE YOUR HELP...thanks

Succeess is never final and failure is never fatal. It's Courage that counts.

 

[NoCoolHandle]

OK I am still working on this one.. Wow. SQL profiler doesn't seem to be able to even see the SQL Server Manager connect !

Also doesn't seem to matter how I login to SQL.. Via the manager or via DMO. The permissions of my NT login account are being used. On servers where I don't have permissions to stop and start services, they don't no matter what tool I use.

SO you shouldn't have to worry about users stopping the services. They will need to be in an NT role that allows them to stop and start services.

What I am wondering right now is how are you connecting? Is the server name the full netbui name in your connections?
WHat network librarys are you using (client and server)?
You can find that information by going
-> Start -> Microsoft SQL Server -> Client Network Utility
and
-> Start -> Microsoft SQL Server -> Server Network Utility



I also have a post at a private MS site to see what the official word is (if they have one).



Rob

 

[kenzai]

Yeah, all my users are in an NT Role...but the problem is why does these users can stop the server wherein they don't have the priviledges in stopping the server. I am using the complete netbui name of the server. I am using SQLDMO to login to the server. The odd thing is I am using the windows authentication mode....was that a bug in SQLDMO.... the very BIG question is why does the server permit a windows user to stop the server.

I have a hunch, that SQLServiceManager connection context is different to the SQLDMO...... what do you think....

Succeess is never final and failure is never fatal. It's Courage that counts.

 

[NoCoolHandle]

This one is really strange...

What it sounds like.. (Correct me if I am wrong)

1. Server Manager will test for correct permissions when you try ot stop or start the SQLService.

2. DMO will let you stop (even if the users permissions are insufficent) the service, but won't let you start it again..

3. All the servers are running in Trusted Mode (no SQL logins allowed - i.e. You can't login as sa as that is a sql login)

4. The services start up as a specific Domain/Computer account..


I can not get my services to exibit these traits -- Until I add permssions that don't seem to be install by default! (so who added them - you might want to check)

What I have bumped into (from just screwing with some servers here) is that you (or someone) might have given permissions to xp_servicecontrol to a sql login without wanting to..

This will have the effect of allowing any user (with execute permission) to "stop" the server as the stored proc works under the permission of the SQLServer startup account.. But when you try to start the service, because it sin't running it wont have permissions to start.. You default back to who you are logged on as..

Take a look at master -> Extended StoredProceedures -> xp_servicecontrol
and check it's permissions. My guess is that either the Guest account or Public group has execute permissions. Remove this permission and everything should work well.

What a B*^%h of an issue to troubleshoot..

Rob

 

[kenzai]

You are correct.. but with my application, the user i am trying to has no access to execute the xp_servicecontrol..... and we are not running in Trusted Mode... you can choose what login type you want..... I really need to troubleshoot this because it is a very high security risk...thank you very much....

Succeess is never final and failure is never fatal. It's Courage that counts.