September 5, 2002 at 7:38 am
This command works from one of our machines, but not on others:
EXEC master..xp_cmdshell 'osql -SMyServer -dMyDb -iMyFile -n'
We ran the osql command from a dos box and discovered that we needed the user and password specified:
EXEC master..xp_cmdshell 'osql -SMyServer -dMyDb -UMyUser -PMyPassword -iMyFile -n'
Why is that? We would like to be able to go without specifying the user and password on all machines.
Steve
Steve Miller
September 5, 2002 at 8:12 am
If you don't specify the -U or -P options when running OSQL, SQL Server 2000 will attempt to connect using Windows Authentication.
I suspect one server is configured for Windows Authentication, and the other isn't.
September 5, 2002 at 8:12 am
If the user executing the "OSQL" command is a member of the administrator group for the server you are trying to connect, and the Builtin/Adminstrator login in sql server has not been disabled, then the user is allowed to logon to SQL without a login and password.
I'm guessing you are a member of the administrator group, but the dos user was not.
-------------------------
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 5, 2002 at 9:01 am
I went to Enterprise Manager on both machines (these are all development machines), went to properties, then the Security tab. Both have "SQL Server and Windows" checked. This leads me to believe both are in mixed mode authentication. So I'm not quite sure why osql using Windows authentication would work on one and not the other.
How would I check to see if a user is member of the administrator group?
Steve
Steve Miller
September 5, 2002 at 9:10 am
On the server is questrion, Click on the "Users and Passwords" under "Control Panel". Then Click on the "Advanced" tab, then click on the "Advanced" button, then expand the "group" folder, then click on the "Administrator". This should display the members of this group.
-------------------------
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 5, 2002 at 9:10 am
If you don't have the ability to bring up the User Manager for Domains (NT 4.0) or Computer Management (Win2K) for that server, run the following command from a command prompt on the server:
net localgroup administrators
This should enumerate the list of user accounts with administrative rights to the server.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
September 5, 2002 at 9:52 am
One of the machines that needed the user and password was mine.
Went to Computer Management (XP). I'm part of the Administrators Group.
Because I'm on XP, I went to User Accounts. The Users tab shows me part of both Debugger Users group and the Administrators Group. If I drill down long enough to get to the groups folder, I find that I'm an Administrator there as well.
I'm stumped.
I don't think it makes a difference, but maybe I should add that this is being called from a C++ command. Here's where it's being formatted for the object:
stuSql.Format(L"EXEC master..xp_cmdshell 'osql -S%s -d%s -UMyUser -PMyPassword -i%s -n'",
sbstrServer.Chars(), sbstrDatabase.Chars(), stuPath.Chars());
Steve
Steve Miller
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply