Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I had installed SQL Server 2008 R2 "SQL Server Database Engine" in virtual machine and I want to connect to it over odbc from other computer (Linux).

I hadn't installed management studio as I wanted minimal possible installation (it is mostly for very basic testing purposes not for real work).

I hadn't installed Management Studio and I don't want to install it.

During installation procedure I provided a user and password for connection however, looks like thing went wrong and I can't login using this user.

Situation:

  1. I have installed "Database Engine" and configured TCP connection
  2. I can connect to it using isql but it reports that user does not exist.
  3. I see in Event Log (of Windows) complains that user does not exist.
  4. I've tried to run import tool and managed to login using XP user on same PC and could run simple queries, however this tool does not allow me to run a statement so I can't run CREATE LOGIN user-name WITH PASSWORD='secret'.

How can I connect to this database and add new users?

Basically at this point I have two options:

  1. Reinstall database, but:

    I don't want to and nothing promises that this time it would work and create a user.

  2. Install management studio I don't want it is huge install for virtual machine.

Question:

Is there any simple command line tool to access DB in place just to run several statements using XP authentication without a huge Management studio tool?

Solution Summary:

  1. Use sqlcmd -Q "create login ..." to run sql statements I need. sqlcmd.exe is installed with "SQL Server Database Engine"
  2. My user wasn't defined correctly during the installation because MS SQL didn't like my password I had given and it didn't alert me on the problem...
share|improve this question
3  
It seems silly to not use the purpose built tools that come packaged with the product just to save a few hundred MB. –  DanBig Nov 18 '10 at 14:40
    
@Dan not in case when you have 1-2GB of virtual disk in virtual machine. Especially when you don't need them. –  Artyom Nov 18 '10 at 15:06
    
You do know you don't HAVE TO install SSMS on that particular VM to access the DB that resides there? –  GregD Nov 18 '10 at 15:09
    
@GregD - but you need to install it somewhere, and basically this is the only Windows machine I have, rest are Linuxes, unless MS released SSMS for Linux :-) –  Artyom Nov 18 '10 at 15:12
4  
@Artyom: As someone who manages numerous SQL Server instances from Sql 2000 - 2008, creating a VM to throw XP (at least) just to run SSMS makes sense. SSMS is your friend..not the enemy you're making it out to be.. –  GregD Nov 18 '10 at 15:17

2 Answers 2

up vote 3 down vote accepted

sqlcmd will allow you to run commands against the server using Windows or SQL Authentication. If something's gone wrong and you've ended up with no windows accounts granted rights to the server then you can start SQL Serve in single user mode to get access and create new accounts.

share|improve this answer
    
is sqlcmd included in "SQL Server Database Engine" or it is part of management studio? Because I searched for all exes in installation and hadn't found this one (or I missed it) –  Artyom Nov 18 '10 at 13:54
2  
@Artyom - for me, sqlcmd is {program files}\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE –  Marc Gravell Nov 18 '10 at 15:10
    
Thanks. I'll give it a try as soon as I get to this computer. –  Artyom Nov 18 '10 at 15:27
    
Thanks you very much, it was on "C:\" even I installed MS SQL on "E:" ... This really helped me. –  Artyom Nov 18 '10 at 20:29
    
I had to go in to single user mode myself. Adding ;-m to the end of the service startup parameters and restarting did it. Make sure to return it to normal once you have your account added. –  Chris Nov 6 '12 at 21:33

On my system, which is SQL 2008 (not R2), sqlcmd.exe is located in "C:\Program Files\Microsoft SQL Server\100\Tools\Binn". If you are using R2, it might be a slightly different path. You could try looking for "osql.exe". osql.exe works mostly the same as sqlcmd but predates sqlcmd.exe and is not quite as full-featured. On my system, osql.exe is in the same Binn directory as sqlcmd.exe.

Even when I intend to administer the server remotely, I find that installing the tools on the server, along with the SQL services, causes much less pain than not installing them. And I've been using SQL Server for 12 years. Certain things (like replication) are very difficult to do by manually writing Transact-sql because the stored procedure calls can be very complex, and often some seemingly trivial utility that you wind up needing is missing.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.