Galin Iliev's blog

Software Architecture & Development

Access Remote SQL Server with SQL Management Studio and Windows Authentication

I've been working with SQL Management Studio since it's release (and even before) and I think it is a very good tool. Especially as in next version there will be IntelliSense.

In my daily work I need to access several remote SQL Servers (over VPN) and some of them require windows authentication. With SQL Server Authentication is easy - just create VPN connection and use SQL Server Management Studio from local machine entering SQL credentials in the wide-known box below:

image

This is not the case with windows authentication. Especially in domain environment it is better to give access to domain groups and users instead of creating SQL ones.

As a workaround (the one I've used 'till today) you can do remote desktop connection to the server and do the job via RDC console. I have struggled with this approach for a long time and although it is not that bad there are some issues working all the time via RDC.

I've tried runas command but somehow my credentials weren't accepted.... but the switch /NetOnly did the job

so I use now

C:\>runas /netonly /user:domainName\userName "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\ssmsee.exe"

and it works...

Hope this helps

Comments (3) -

  • Martin Kulov

    4/10/2008 8:32:04 AM | Reply

    Is your computer joined to the AD that you connect to?

  • Galcho

    4/10/2008 10:26:11 AM | Reply

    No, it isn't joined to AD. Maybe this is why it cannot authenticate the runas.
    This is where /netonly parameter comes in play - passed credentials are used only on net requests - where exactly I need them. I haven't played if credential authentication is done before net request.

  • Gavin

    8/5/2008 6:10:19 PM | Reply

    I'm stealing this for my blog. Smile Credit where credit is due of course.

    This worked extremely well for me. Similar situation where I'm not part of the domain (or any domain) but also where I only have TCP port 1433 access to the server. This prevents the other shortcuts of "net use" to the IPC$ service.

Loading