SQLCMD problems

Question

SQLCMD problems

schleep

SSChampion

Points: 12582
More actions
January 21, 2014 at 7:23 am

#303431

What I'm trying to accomplish is to write network logons / logoffs to a SQL server.
We've set up a group policy on the OU, and want to call SQLCMD to write the info to a table. Sounds simple enough. We have logon.cmd and logoff.cmd which set the parameters and call SQLCMD.
I'm trying to call a local copy of SQLCMD.exe -- we don't want users having access to the C:\Program Files\... -- and getting various errors:
- if I call SQLCMD v. 2009.100.2500.0, I get SQLCMD is not a valid Win32 app.
- if I call SQLCMD v. 2009.100.1600.0, I get BatchParser.dll (2009.100.1600.1) is not a valid Windows image
We're using SQL 2008 R2 sp1 on a Win2008 R2 sp1 box.
How can I make this work?
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

I don't think it'll work if you just lift sqlcmd.exe and copy it somewhere else. It needs access to all sorts of DLLs and other stuff. Why can't you just create a desktop shortcut to the file in the prohibited folder?

John

schleep SSChampion Points: 12582 More actions · Answer 2

"...just create a desktop shortcut to the file in the prohibited folder?"

I'm sorry, I don't follow.

The logon/logoff.cmd files called by the policy are sitting on the SQL Server in question, and should be calling SQLCMD on that server, no?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

OK, I didn't quite understand first time. You have these two .cmd files that are triggered automatically whenever a logon or logoff event occurs (presumably on AD) - is that right? If that's the case, no users need to go anywhere near sqlcmd.exe or the .cmd files, do they?

John

schleep SSChampion Points: 12582 More actions · Answer 4

Yes, that's correct, they're fired by AD group policy.

So yes, I don't think the .cmds are called in the users' context. In fact, they would likely be running under a domain admin account... so privs shouldn't be an issue, right?

Going to work w/ my domain admin, I'll get back to you if still unresolved.

schleep SSChampion Points: 12582 More actions · Answer 5

Still no joy.

Maybe we're going about this the wrong way.

Let me back up: A Group policy is set, and what we want to happen is

1) A user logs into domain

2) A row is inserted in a SQL Server database table on same domain in realtime. User does not necessarily have access to SQL Server.

Any suggestions? We're about to revert to the tried and true parsing of logs....

P

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

You haven't said what exactly isn't working. I suspect that your problem is how to fire triggers from events in AD. If that's the case, you'd be better off posting on a more suitable forum.

John

schleep SSChampion Points: 12582 More actions · Answer 7

What isn't working is getting SQLCMD to run in the context of the user that's login in/out.

I had assumed that the policy would execute under a system/admin account.

But you're right, I'll seek help in more appropriate fora.

Thanks John.

Perry Whittle SSC Guru Points: 233825 More actions · Answer 8

schleep (1/22/2014)
What isn't working is getting SQLCMD to run in the context of the user that's login in/out.
I had assumed that the policy would execute under a system/admin account.
But you're right, I'll seek help in more appropriate fora.
Thanks John.

Embed the query in a SP and create the stored proc with execute as owner, that way it wont care who executes it 😉

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

paul.knibbs SSCoach Points: 15320 More actions · Answer 9

schleep (1/22/2014)
What isn't working is getting SQLCMD to run in the context of the user that's login in/out.
I had assumed that the policy would execute under a system/admin account.

If you're doing it via a conventional login script, then no, that doesn't execute as a sysadmin account--it wouldn't be terribly useful if it *did*, because login scripts are supposed to do stuff that's specific to the user logging in, not some random admin user. This is probably why it's not working.

schleep SSChampion Points: 12582 More actions · Answer 10

It's getting SQLCMD to run that's the problem. Used to be so simple with ISQL/OSQL. Just copy the .exe, and call it.

We've got it working now using vbs to call our sproc. Yay!

Thanks for the input, gents.