Okay, a database question

[R00k]

I have a SQL database that is running on MSDE, and I can't use any remote management tools on it to access it (no Enterprise Mgr, Query Analyzer, etc), because for some reason it won't authenticate.

It was setup by a guy who no longer works here.

The only way I can connect to the database to run any queries, is by using OSQL on the local machine, and authenticating with the local Administrator account on the computer.

The problem is, I'm useless with OSQL for the most part.

I want to find out everything about this database -- all the table names, jobs, etc., like I would be able to see with Enterprise Manager -- just by using the OSQL utility, and generic SQL/T-SQL statements. I don't know what stored procedures are in the database or anything, although I'm sure most of the standard ones are there.

Can anyone tell me how I might be able to find out everything about this database (so I can actually write some queries for it), using only OSQL?

Thanks for any help.

*crosses fingers*

[^misantropia^]

OSQL (read: Transact-SQL) allows you to execute regular SQL statements, no? So shouldn't normal MSSQL introspection do the job?

[R00k]

Yea, but that assumes I know my T-SQL and SPs -- which I don't.

I think I found something with a little searching though:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

I think this might work...

[^misantropia^]

I was just re-reading your post (didn't parse fully the first time). Yeah, you can dig everything up from INFORMATION_SCHEMA; it contains a lot more than just table layouts.

[plained]

i havnt read any of ur post but i would use remote management tools it to access it like Enterprise Mgr and Query Analyzer.

then i would ask the guy who set it up.

one thing you could do to connect to the database to run queries. You could use OSQL on the local machine, and authenticating with the local Administrator account on the computer.

I think you should find out everything about this database -- all the table names, jobs, etc., you need to be able to see with Enterprise Manager -- just by using the OSQL utility, and generic SQL/T-SQL statements.

also you need to know what stored procedures are in the database and find out if most of the standard ones are there.

So really you need to be able to find out everything about this database.

then you can actually write some queries for it.

hope that helps

[R00k]

I was just re-reading your post (didn't parse fully the first time). Yeah, you can dig everything up from INFORMATION_SCHEMA; it contains a lot more than just table layouts.

It looks like the query I ran only gave me a list of system tables - or maybe just a list of tables in the master db?

Hm, perhaps I should just specify the db name and see if it gives me what I want..... what a revelation, lol.

[^misantropia^]

http://dev.mysql.com/doc/refman/5.0/en/ ... chema.html

It's a link to the MySQL documentation about INFORMATION_SCHEMA because I can't seem to find any decent MSSQL documentation on the subject. No matter, it's an ANSI standard and - rather uncommon for anything ANSI SQL - implemented quite uniformly across database vendors.

Hm, perhaps I should just specify the db name and see if it gives me what I want..... what a revelation, lol.

"SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%DBNAME%'" probably does what you want. ANSI SQL doesn't have the concept of multiple databases, only tables, so most vendors concatenate the database and table name (something like db1_table1).

[R00k]

Thanks for the help.

Let me ask you this:

Using T-SQL, do you know how I would add a new user with full access rights (equivalent to an SA account)?

If I could do this, I could add permissions to my domain account, and could probably access the db remotely with Enterprise Manager from another machine. That would be ideal.

[Foo]

Silly question possibly: Have you tried installing SQL Manager locally and trying to access the DB?

[Foo]

Also damn you, I've been dealing with SQL/Development silliness all day already

[R00k]

Yea, unfortunately it's a SQL 7 database, with no tools installed. I can't install the SQL 2000 tools on the server, because it will want to replace the MDAC with a different version and everything else, and requiring a restart to do that.

But it's moot anyway finally!

Using OSQL I finally managed to create a user with the sysadmin role, so I can now connect to the database with Enterprise Manager installed on my PC, and do everything I need. I'm very happy about that.

So I've now created a user account that only has read access to the database I'm trying to access.

Now the hard part begins!

What I'm trying to do is write and script some queries that I can automate, that will dump all the info from a certain table in this database, to a tab-separated file, which I can then parse out with some Perl scripts.

The Perl parsing is easy as pie. My problem now, is finding a way to dump the data I need into a file in a format that I can actually use.

OSQL's output file is just plain shitty. As far as I can tell, it is completely useless for any purpose whatsoever, unless you want to write a program yourself that completely reformats it.

So I've decided to give BCP a shot now. Unfortunately, the database I'm accessing has a hyphen in the name, and BCP is throwing up a syntax error near the hyphen every time I try it. Putting quotes around the db/table name doesn't seem to matter.

This sort of thing REALLY doesn't seem like it should be nearly this hard. There's probably some standard tool out there that I know nothing about that makes it all easy.

[R00k]

this is really starting to piss me off

[^misantropia^]

`osql [insert database and query switches] -w 2048 -s "," -o output.csv` ?

Won't generate a true CSV file but should be Good Enough(TM) in most cases.

[^misantropia^]

Alternatively, use something like | as delimiter and split by that:

Code: Select all

while (<STDIN>) {
  my @fields = split /\|/;
  # do something
}

[Foo]

I'm pretty sure there's a specific MS tool to take backups and run jobs against an MSDE SQL installation.

But I'll be fucked if I can remember what it's called. I remember this because we recently had to implement it on our AV server.

I'll try and remember to look this up on Monday for you.

[R00k]

`osql [insert database and query switches] -w 2048 -s "," -o output.csv` ?

Won't generate a true CSV file but should be Good Enough(TM) in most cases.

Yea, I'm already doing something similar:

Code: Select all

osql /S NAS-SYSWB01 /U dbreader /P password /d ePO_NAS-SYSWB01 /h-1 /n  /w 2600 /s "<typed 'tab' char here>" /i selectall.sql /o \tbl_ComputerProperties.txt

I got it to run and spit out something semi-usable, except for two minor things: If the SQL field in the table is defined w/ a 355-char limit, then I get 250 spaces after a 5-char word. No biggie, but it would be nice to be able to clean that up.

Secondly, the above command works fine, as long as it's on that Windows 2000 Server that the db is on. But if I try to run it on an XP or Server 2003 machine, when it gets to that "tab" character, it tries to use the CMD-prompt "autocomplete" where it injects the next directory name, and it actually puts that in the command and jacks it all up.

I've tried using \t, "\t", ^\t, \\t, "\<typed tab char>" etc, but haven't had any luck getting around it.

It always seems to be the little things that frustrate you. lol

[Foo]

Could you seperate with another character instead?

[Foo]

Also back to the previous authentication issue, I was reading up and it looks like with MSDE you need to be a local administrator on the server to gain remote access to database.

So passing the logon credentials as NAMEOFSERVER\Administrator or adding YOURDOMAIN\YOURACCOUNT to the windows local administrators should let you connect.

I realise you may have covered this or don't care anymore since you're getting places with osql

[R00k]

Also back to the previous authentication issue, I was reading up and it looks like with MSDE you need to be a local administrator on the server to gain remote access to database.

So passing the logon credentials as NAMEOFSERVER\Administrator or adding YOURDOMAIN\YOURACCOUNT to the windows local administrators should let you connect.

I realise you may have covered this or don't care anymore since you're getting places with osql

You know, it's really weird. I have to login to the server with my domain account that is an admin on the server; then I have to use runas to run a command prompt as the local administrator account (server\administrator), and then when I run osql, I just give it the password of that local administrator account again, and it's happy.

But if I actually LOGIN to the server as the local admin acct, and run a command prompt normally, osql will not let me connect for any reason. It's really strange.

Could you seperate with another character instead?

Yea, I guess I could separate with something else. I just really like using tabs because it looks so much cleaner, even in a plain text editor.

Maybe I'll go with something less problematic anyway.

[R00k]

BTW, this is the McAfee ePolicy Orchestrator server database that I'm trying to get into. I'm wanting to use the data as part of a larger inventory database.

[Foo]

Haha, EPO is exactly the server we've been messing with this week too.

I'll have a play around with ours on monday and see if I get anything similar.

BTW as for the machine accounts/authentication - It still has its domain trust functioning correctly?

[R00k]

Yep, it's completely weird.

[Foo]

Bah, stuffed I'm afraid mate. We've moved up to the newer version with a fresh install and it's not MSDE, it's the new/different 'compact' version apparently. Comes with its own management tools.

[R00k]

That's cool -- we're going to be upgrading soon and I didn't know what the newer version ran on. I've been wanting to take a look at the new SQL "Lite" or whatever they're calling it. A buddy of mine who's a developer has told me a lot of things about it that sound pretty awesome, but I haven't had a chance to look at it yet.