One last xp_cmdshell Q (sorry)

I run the below on the box that has psExec installed as sa (dbo) in QA.
The MSSQLSERVER service is set to Local Account.
Domain\Administrator will run c:\psexec \\10.2.27.230 -I cmdmgr_Resume.bat
from a CmdExec job sucessfully. I run same from command prompt successfully.
Do I have to set MSSQLSERVER agent to start as admin.?
Or can I explicitly say exec master.Domain\Administrator.cp_cmdshell or
similar?
Even our DBA is stumped!!
EXEC xp_cmdshell 'c:\psexec \\10.2.27.230 -I cmdmgr_Resume.bat' ;
output
---
NULL
PsExec v1.60 - Execute processes remotely
Copyright (C) 2001-2005 Mark Russinovich
Sysinternals - www.sysinternals.com
NULL
Access is denied.
Connecting to 10.2.27.230...
Couldn't access 10.2.27.230:
NULL
It looks like psExec doesn't have permission but it is SQL QA that is the
sourcer of the problem.Local Account? or Local System? The SQL Server service must execute with
the credentials needed to execute commands on the other computer. It cannot
execute as Local System. It must execute as a domain user account.
WARNING!!! It is extremely dangerous to use xp_cmdshell in this way. It is
even more dangerous to run as a domain admin. If you're a consultant, be
sure you have a battalion of lawyers in your pocket and review your
professional insurance contract, because it would be a simple thing for a
smart lawyer to prove gross negligence on your part.
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:642C7D5B-DA48-427D-9535-0BF66E90ADA4@.microsoft.com...
> I run the below on the box that has psExec installed as sa (dbo) in QA.
> The MSSQLSERVER service is set to Local Account.
> Domain\Administrator will run c:\psexec \\10.2.27.230 -I cmdmgr_Resume.bat
> from a CmdExec job sucessfully. I run same from command prompt
successfully.
> Do I have to set MSSQLSERVER agent to start as admin.?
> Or can I explicitly say exec master.Domain\Administrator.cp_cmdshell or
> similar?
> Even our DBA is stumped!!
> EXEC xp_cmdshell 'c:\psexec \\10.2.27.230 -I cmdmgr_Resume.bat' ;
> output
> ---
> NULL
> PsExec v1.60 - Execute processes remotely
> Copyright (C) 2001-2005 Mark Russinovich
> Sysinternals - www.sysinternals.com
> NULL
> Access is denied.
> Connecting to 10.2.27.230...
>
> Couldn't access 10.2.27.230:
> NULL
> It looks like psExec doesn't have permission but it is SQL QA that is the
> sourcer of the problem.|||thx again, what are the alternatives, create an account and lock it down,
maybe? or
"Brian Selzer" wrote:

> Local Account? or Local System? The SQL Server service must execute with
> the credentials needed to execute commands on the other computer. It cann
ot
> execute as Local System. It must execute as a domain user account.
> WARNING!!! It is extremely dangerous to use xp_cmdshell in this way. It i
s
> even more dangerous to run as a domain admin. If you're a consultant, be
> sure you have a battalion of lawyers in your pocket and review your
> professional insurance contract, because it would be a simple thing for a
> smart lawyer to prove gross negligence on your part.
> "marcmc" <marcmc@.discussions.microsoft.com> wrote in message
> news:642C7D5B-DA48-427D-9535-0BF66E90ADA4@.microsoft.com...
> successfully.
>
>|||Create an external process to do whatever it is you need to do. Then you
don't have to worry about the xp_cmdshell security hole. Another solution
is to use replication, but if you can't, then you can queue up the
information in a table and create a dequeue process to move it. Both of
these solutions is more robust, because you can still collect information if
the other system is down.
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:97BD8606-7F5A-4904-B13C-533FE167A77D@.microsoft.com...
> thx again, what are the alternatives, create an account and lock it down,
> maybe? or
> "Brian Selzer" wrote:
>
with
cannot
is
be
a
QA.
cmdmgr_Resume.bat
or
the|||On Wed, 7 Sep 2005 05:46:10 -0700, marcmc wrote:

>I run the below on the box that has psExec installed as sa (dbo) in QA.
>The MSSQLSERVER service is set to Local Account.
>Domain\Administrator will run c:\psexec \\10.2.27.230 -I cmdmgr_Resume.bat
>from a CmdExec job sucessfully. I run same from command prompt successfully
.
>Do I have to set MSSQLSERVER agent to start as admin.?
>Or can I explicitly say exec master.Domain\Administrator.cp_cmdshell or
>similar?
>Even our DBA is stumped!!
Hi marcmc,
(S)he should check out what BOL says about services accounts.
If you really want to do it like this, than the least unsafe way is to:
a) Have your network administrator set up a domain account for use by
the SQL Server service. Make sure to change the password policy to not
automatically age the password.
b) Give this account all privileges required for SQL Server operation,
plus all privileges required to run your batch file. Don't give any
other privileges to this account. If your version of windows allows it,
disable the posibility to log on using this account as a normal user
(i.e. make it an account that can only be used by services)
c) Change the startup parameters for your SQL Server instance to log in
using this account instead of logging in as Local System.
d) Keep your fingers crossed :-)
But I agree with Brian that there are other ways to do this, and that
they are probably better!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

OLEDB question

This may be a newbie question but here it goes. I have SQL Server 2000 running on a windows server 2003 machine. When i have my database local, on MSDE everything is fine. But when i switch my code to move to the server, (after i've attached the database within the server) it tells me that server either does not exist or login failed. Now here is my connection statement

OleDbConnection m_conn;

string connectionString = "Provider=SQLOLEDB;Data Source=SERVER1;Initial Catalog=workups;Integrated Security=SSPI";

m_conn = new OleDbConnection(connectionString);

Now like i said when i change the Data Source from (local) to SERVER1 it gives me this error, however on the server all the data is fine, and i can view it locally on the server. Anyone know what's wrong or if it's a coding issue?

Thanks

Dan

What's the error message?|||

Error message in my event log, i log the actual exception here it is

System.Data.OleDb.OleDbException: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr)

at System.Data.OleDb.OleDbConnection.InitializeProvider()

at System.Data.OleDb.OleDbConnection.Open()

at DotThisSoftware.SqlClient.DatabaseFactory.Open() in C:\Documents and Settings\drux\My Documents\Visual Studio Projects\DotThisSoftware.SqlClient\DatabaseFactory.cs:line 88

THanks

Dan

|||I see, have you verified, since you're using Integrated Security, that the current user running the application can logon to the remote server?|||

Ok, i can't log into the server through visual studio, would you know how to fix this or know of a kb article that would help me.

Dan

|||Well, I need for information about the SQL Server: is it 2000, 2005, Express and is it listening on typical port 1433 and can you ping the server from the client using the host name?|||

More info:

stated earlier " I have SQL Server 2000 running on a windows server 2003 machine."

I can ping the server, and yes it's running on 1433 standard port. I still have no connectivity from my workstations for the sql server. Thanks for your help.

|||

It sounds like a security issue then. You may want to try connecting to the server via the client via a tool such as Query Analyzer to determine if your credentials are sufficient to login.

Also, may I ask why you're not using the native ADO.NET SqlClient provider instead?

|||

Please check the following things:

1) what's the protocol enabled on your server? and your client machine? You must have at least on matching protocols on both sides.

2) any firewall on the server? Try disable firewall and do test to isolate the problem. Port 1433 and file sharing should be on the firewall's exception list.

check this blog for more info: http://blogs.msdn.com/sql_protocols

|||

OK here's the problem...

I found out from my lovely network administrator earlier that in fact the service packs weren't installed as she thought. Once these service packs were installed we had access. Don't you just love it when something isn't done that supposed to be.

Thank you though for your help.

Dan

Oledb Provider having problem in MS SQL Server 2005

Hi,

I try to access the table "UserGroup" in my local sql server 2005 database. However I get this error message
"Invalid object name 'UserGroup'. "

I'm using MS Visual Studio 2005 and MS Sql Server 2005. I did install the latest Sql Native. In fact I just migrate from ASP.NET to ASP.NET 2.0 . It is working if my connection string is point to my "online host" without any error.

'Online Connection String - Working
<addkey="OLEDB"value="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=abc;Initial Catalog=mydatabase_db;Data Source=xxx.xx.xx.xx,xxxx; Password=xxxx;Connect Timeout=3600"/>

'Local Connection String - Not working
(SQL Authentication)
<addkey="OLEDB"value="Provider=SQLNCLI;Trusted_Connection=No;UID=calvin;Initial Catalog=mydatabase_db;Data Source=CALVINNB; Password=xxxxxx"/>
OR
(Windows Authentication)
<addkey="OLEDB"value="Provider=SQLNCLI;Server=CALVINNB;Database=mydatabase_db;Trusted_Connection=yes"/
Is the Oledb provider having problem in MS SQL Server 2005?

Any help would be more appreciate.

Calvin

First thing I would try it to make sure you do have a table named UserGroup, and also try prefixing with with the owner.

Next, you should be using the SQL provider and not the OLEDB providers for SQL Server.

|||Yes, I do have a table named UserGroup. If I change the OLEDB provider to SQL provider, then I have a lot of code need to be change and debug. Is there any way to solve this problem? And I don't understand why I use my "Online" connection string(bottom) without error? Even the provider isSQLOLEDB.1?

<addkey="OLEDB"value="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=xxx;Initial Catalog=mydatabase_db;Data Source=xxx.xx.xx.xx,xxxx; Password=xxxxxx;Connect Timeout=3600"/>

The difference to my local connection is just the "UID", "Password" and Data Source only. I'm sure that the login account for local is created perfectly. The host and my computer also using MS SQL Server 2005.

|||

EDUStreet:

Yes, I do have a table named UserGroup. If I change the OLEDB provider to SQL provider, then I have a lot of code need to be change and debug.

OLEDB provider is meant to be used for older data sources. For SQL Server 7.0+ you should really be using the SQL Providers, that is what it is there for. In terms of coding changes there should not be too many apart from renaming all the declarations and instantiations from Oledb to Sql.

One of the best ways to make sure you got the right connection string is to drag and drop something like a gridview or sqldatasource and go through the wizard to connect to your datasouce and use the auto generated connection string

Here is a web site that has all connection string formats

http://www.connectionstrings.com/?carrier=sqlserver2005

|||

Finally, I change all the code which is from OLEDB provider to SQL provider

This is the connection string i'm using now.

<addkey="OLEDB"value="User ID=xxxx;Initial Catalog=mydatabase_db;Data Source=xxx.xx.xx.xx,xxxx; Password=xxxx"/>

|||

EDUStreet:

"The ConnectionString property has not been initialized. "

When you create the SqlConnection object you need to supply the connection string. Please ensure you have done this.

|||

Thanks Jimmy :)

OLEDB Bookmarks

I generated an ATL OLEDB Consumer with the VS 2005 wizard. The table is very simple and is located on my local SQL Server. It has two varchar columns and no keys or constraints. I can modify, insert and delete rows with no problem.

Now I want to use bookmarks. I added the following to the accessor class:

1. CBookmark<4> m_bookmark; // member variable

2. pPropSet->AddProperty(DBPROP_IRowsetLocate, true); // rowset property

3. BOOKMARK_ENTRY(m_bookmark) // column map entry

Now I can delete but I can no longer modify or insert. The error is DB_E_ERRORSOCCURED and the status of the two columns after a setdata or insert is DBSTATUS_E_UNAVAILABLE. This is the only error information available.

When I comment out the bookmark entry from the column map, I can modify, insert and delete again. The problem appears to be in how IRowsetChange handles bookmarks. I tried adding bookmarks to other tables using the above 3 steps and they also refuse to modify or insert with bookmarks enabled.

The bookmarks appear to be correct, as I can do a successful MoveToBookmark for any row in the table.

My environment: Windows XP SP2, VC++ 2005, SQL Server 2005 Express, WTL 8.0

Any ideas?

I guess you are doing the following when you modify:

Request IRowsetUpdate/IRowsetChange (get a rowset off that) and bind the columns (IColumnsInfo::GetColumnInfo) to create the accessor, you will see bookmark column. Detect this column and not bind it (use column flags to detect it) when you create the accessor. Or also try to set the status to DBSTATUS_S_IGNORE (so that when you update it will be ignored)

Hope this works

|||

Excellent suggestions, Raj! I created a new macro called bookmark_entry_status in atldbcli.h and in my application I set the status of the bookmark status variable to DBSTATUS_S_IGNORE. Now I can modify, insert and delete consumers that have bookmarks enabled. Here is the macro:

#define BOOKMARK_ENTRY_STATUS(variable, status) \

COLUMN_ENTRY_TYPE_STATUS(0, DBTYPE_BYTES, status, variable##.m_rgBuffer)

Thanks for the ideas. It set me on the proper path to the solution.

OLEDB Bookmarks

I generated an ATL OLEDB Consumer with the VS 2005 wizard. The table is very simple and is located on my local SQL Server. It has two varchar columns and no keys or constraints. I can modify, insert and delete rows with no problem.

Now I want to use bookmarks. I added the following to the accessor class:

1. CBookmark<4> m_bookmark; // member variable

2. pPropSet->AddProperty(DBPROP_IRowsetLocate, true); // rowset property

3. BOOKMARK_ENTRY(m_bookmark) // column map entry

Now I can delete but I can no longer modify or insert. The error is DB_E_ERRORSOCCURED and the status of the two columns after a setdata or insert is DBSTATUS_E_UNAVAILABLE. This is the only error information available.

When I comment out the bookmark entry from the column map, I can modify, insert and delete again. The problem appears to be in how IRowsetChange handles bookmarks. I tried adding bookmarks to other tables using the above 3 steps and they also refuse to modify or insert with bookmarks enabled.

The bookmarks appear to be correct, as I can do a successful MoveToBookmark for any row in the table.

My environment: Windows XP SP2, VC++ 2005, SQL Server 2005 Express, WTL 8.0

Any ideas?

I guess you are doing the following when you modify:

Request IRowsetUpdate/IRowsetChange (get a rowset off that) and bind the columns (IColumnsInfo::GetColumnInfo) to create the accessor, you will see bookmark column. Detect this column and not bind it (use column flags to detect it) when you create the accessor. Or also try to set the status to DBSTATUS_S_IGNORE (so that when you update it will be ignored)

Hope this works

|||

Excellent suggestions, Raj! I created a new macro called bookmark_entry_status in atldbcli.h and in my application I set the status of the bookmark status variable to DBSTATUS_S_IGNORE. Now I can modify, insert and delete consumers that have bookmarks enabled. Here is the macro:

#define BOOKMARK_ENTRY_STATUS(variable, status) \

COLUMN_ENTRY_TYPE_STATUS(0, DBTYPE_BYTES, status, variable##.m_rgBuffer)

Thanks for the ideas. It set me on the proper path to the solution.

OLEDB Bookmarks

I generated an ATL OLEDB Consumer with the VS 2005 wizard. The table is very simple and is located on my local SQL Server. It has two varchar columns and no keys or constraints. I can modify, insert and delete rows with no problem.

Now I want to use bookmarks. I added the following to the accessor class:

1. CBookmark<4> m_bookmark; // member variable

2. pPropSet->AddProperty(DBPROP_IRowsetLocate, true); // rowset property

3. BOOKMARK_ENTRY(m_bookmark) // column map entry

Now I can delete but I can no longer modify or insert. The error is DB_E_ERRORSOCCURED and the status of the two columns after a setdata or insert is DBSTATUS_E_UNAVAILABLE. This is the only error information available.

When I comment out the bookmark entry from the column map, I can modify, insert and delete again. The problem appears to be in how IRowsetChange handles bookmarks. I tried adding bookmarks to other tables using the above 3 steps and they also refuse to modify or insert with bookmarks enabled.

The bookmarks appear to be correct, as I can do a successful MoveToBookmark for any row in the table.

My environment: Windows XP SP2, VC++ 2005, SQL Server 2005 Express, WTL 8.0

Any ideas?

I guess you are doing the following when you modify:

Request IRowsetUpdate/IRowsetChange (get a rowset off that) and bind the columns (IColumnsInfo::GetColumnInfo) to create the accessor, you will see bookmark column. Detect this column and not bind it (use column flags to detect it) when you create the accessor. Or also try to set the status to DBSTATUS_S_IGNORE (so that when you update it will be ignored)

Hope this works

|||

Excellent suggestions, Raj! I created a new macro called bookmark_entry_status in atldbcli.h and in my application I set the status of the bookmark status variable to DBSTATUS_S_IGNORE. Now I can modify, insert and delete consumers that have bookmarks enabled. Here is the macro:

#define BOOKMARK_ENTRY_STATUS(variable, status) \

COLUMN_ENTRY_TYPE_STATUS(0, DBTYPE_BYTES, status, variable##.m_rgBuffer)

Thanks for the ideas. It set me on the proper path to the solution.

OLE DB Provider no registered on local machine

I am receiving the following error when trying to run my report from the
server:
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
Cannot create a connection to data source 'SALESLOGIX'.
(rsErrorOpeningConnection) Get Online Help
The 'SLXOLEDB.1' provider is not registered on the local machine.
No error information available: REGDB_E_CLASSNOTREG(0x80040154).
I checked and this provider seems to only be available under the profile it
was installed. That being said I installed it under both profiles for my
service user and DB user. I checked the provider by testing it with a udl
file and it worked on both profiles. I rebooted the machine and I am still
having the same issue.Ok I found out my problem. This OLE DB provider has extra registery entries
that exist for the users profile. I installed the full application as the
MSRS user and configuerd the applications DB connection validated the
connection and the report now works.
"Ben Holcombe" wrote:
> I am receiving the following error when trying to run my report from the
> server:
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> Cannot create a connection to data source 'SALESLOGIX'.
> (rsErrorOpeningConnection) Get Online Help
> The 'SLXOLEDB.1' provider is not registered on the local machine.
> No error information available: REGDB_E_CLASSNOTREG(0x80040154).
> I checked and this provider seems to only be available under the profile it
> was installed. That being said I installed it under both profiles for my
> service user and DB user. I checked the provider by testing it with a udl
> file and it worked on both profiles. I rebooted the machine and I am still
> having the same issue.