locked
Can login domain instance of SQL server with local SSMS. RRS feed

  • General discussion

  • Hi Team,

    I am starting with my though here ----------

    what we are using rightnow to connect a domain instance of SQL server from local SSMS. few object are given below-

    1- SQL server authentication - if we are using SQL server authentication then first click on connect to server option in SSMS then we got a small window. in this window we choose database engine in server type box and type appropriate name of the server in server name box after then select authentication as SQL server authentication then type a user name and password. in this way we can connect a domain instance of SQL server from our local desk.

    1- Window server authentication - if we are trying to connect a domain SQL server instance from our local SQL server then we have not option in SQL server to connect from SSMS but we can also do it by creating batch file then open SSMS and now we can connect domain instance.

    my question is if we have a option in SSMS to connect a domain instance from SQL server authentication just select it then why not for windows.

    when we tring to use windows authentication to connect a domain instance why we need to create batch file then click on this and then open SSMS after then we can login from window authentication.

    If any one using inbuild feature of SSMS to connect domain instance from local desk SSMS without creating a batch file then please share it.

    and if any one has more though on this then it will be a good discussion point. thanks

     

     


    Asheesh pandey SQL server DBA

    Monday, February 4, 2013 11:38 AM

All replies

  • Hello Asheesh,

    I'm not sure what your issue is? Are you not on a trusted domain that can use windows authentication? What is in the batch file? What error are you getting?

    -Sean


    Sean Gallardy | Blog | Twitter

    Monday, February 4, 2013 2:32 PM
    Answerer
  • Thanks for your reply Sean. actually, i am not getting any error from local SSMS via batch file. my question was is there any option in SQL server to connect a domain instance without creating a batch file via window authentication.


    Asheesh pandey SQL server DBA

    Tuesday, February 5, 2013 3:32 PM
  • Asheesh,

    Yes, there is nothing that needs to be done to connect to an instance using windows authentication. The reason you're using a batch file (my assumption if you open up the batch file and look) is probably because your current domain account is not the one with permissions to SQL Server. If you take a look at the batch file, it'll probably have a 'runas /user:' command that ends with running ssms or starts ssms with the admin windows token.

    Can you post what the batch file has?

    -Sean


    Sean Gallardy | Blog | Twitter

    Tuesday, February 5, 2013 3:35 PM
    Answerer
  • Hi Sean,

    My batch file is working fine to connect a domain instance via SSMS but is there any way to connect domain instance other than batch file simply from SSMS like SQL authentication. 


    Asheesh pandey SQL server DBA

    Tuesday, February 5, 2013 3:41 PM
  • Asheesh,

    Please re-read my last post. There is nothing that needs to be done - you don't need a batch file to do anything special. All you need to do is select windows authentication from the dropdown box on the connection form just like you would do for SQL Authentication. I'm not sure where the idea of needing a batch file to do this comes from, but it's a normal built it item.

    If you re-read my last post you'll see that the reason I think you need the batch file is because of the way your network/AD/etc is architected.

    Let me reiterate one more time, there is nothing that needs to be done other than open up SSMS and choose windows auth on the connection screen.

    -Sean


    Sean Gallardy | Blog | Twitter

    Tuesday, February 5, 2013 3:48 PM
    Answerer
  • Thanks Sean for your clarification, i was trying to connect different domain SQL instance from my local PC via VPN and both are in different -2 domain. thanks

    Asheesh pandey SQL server DBA

    Wednesday, February 6, 2013 10:55 AM
  • SSMS provides an option to connect to a SQL server instance using windows authentication, and this option automatically uses the credentials used to launch SSMS. As far as I am aware SSMS itself does not include any options to specify a different set of credentials.

    If you need to connect to a SQL server using a different set of windows credentials from the one you are logged in with then you can either use the 'shift right click, run as different user' option when launching SSMS, or call the SSMS executable with a batch file using the RunAs command to specify an alternate set of credentials.

    Tuesday, February 12, 2013 10:36 PM
  • I think I understand what you are asking... Is there a way to connect to a SQL Server on a domain that is different and not trusted to the domain where your workstation is connected.

    Download SysInternals ShellRunAs ..  follow their instructions to register the application on your workstation.

    After it is registered there will be a new menu item in SSMSs context menu...  For example.. 

    • Click on Start
    • Click on All Programs
    • Click on the folder with your version of Microsoft SQL Server
    • Shift+RightClick on SQL Server Management Studio to show the context menu
    • Click on Run As Different User (netonly)... and you will be presented with a Login window
    • Type in your credentials to the domain where the SQL Server is located and you should be good to go..

    -Daniel

    Wednesday, July 31, 2013 8:24 PM