Problem in executing xp_cmdshell with Least Privileged SQL Login account in SQL 2005

Hi,

I have a least privileged SQL Login “Client” and have granted execute rights on XP_Cmdshell SP at master db. When I execute master.. XP_Cmdshell ‘dir’ I’m getting the below error.

Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1

The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.

Please note it is SQL Login account and not windows account. I have checked everywhere for similar problem and no luck.

Thanks for you help in advance

With regards

GK

Here are a few links that seem to cover your situation:

XP_CMDSHELL error
http://www.dbnewsgroups.net/group/microsoft.public.sqlserver.programming/topic20720.aspx

Eralper's Blog on Software Development
http://www.kodyaz.com/blogs/software_development_blog/archive/2006/11/23/478.aspx

The problem of xp_cmdshell_proxy_account
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=576326&SiteID=17

Problem in executing xp_cmdshell with Least Privileged SQL Login account in SQL 2005

Hi,

I have a least privileged SQL Login “Client” and have granted execute rights on XP_Cmdshell SP at master db. When I execute master.. XP_Cmdshell ‘dir’ I’m getting the below error.

Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1

The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.

Please note it is SQL Login account and not windows account. I have checked everywhere for similar problem and no luck.

Thanks for you help in advance

With regards

GK

See my reply to your other related thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1157356&SiteID=1

You need to read the Books Online articles I pointed out in my answer there.

Thanks
Laurentiu

Problem in deployment on SSAS 64 bit with Oracle 10g 32 bit client (as datasource)

Hi,

I installed SQL Server 2005 64 bit and have Oracle 10g 32 bit client,
I am creating a cube based on Oracle datasource by connecting Oracle 10g 32bit client.

when I build and process cube it Done successfully but when I deploye it to server then it gives following error.

Error 1 Errors in the high-level relational engine. The following exception
occurred while the managed IDbConnection interface was being used: Attempt
to load Oracle client libraries threw BadImageFormatException. This problem
will occur when running in 64 bit mode with the 32 bit Oracle client
components installed.. 0 0

Error 2 Errors in the high-level relational engine. A connection could not
be made to the data source with the DataSourceID of 'Oracle DW Call Detail',
Name of 'Oracle DW Call Detail'. 0 0

any suggestion regarding this ?

Hi Kawish,

Did you solve this prolem? I am having the same problemss as you describe here.

look forward to hearing from you..

Problem in connecting after service pack 3a installed in MSDE 2000

Hi All,
I have installed MSDE 2000 server and i installed service pack
3a. The server machine has SQL Server 2000 client connectivity. When i
connect from client machine to Server machine it is showing error.
[Microsoft ] [ODBC SQL Server Driver] [TCP/IP sockets]
SQL Server does not exists or access denied.
I installed Servicepack 3a with out DISABLENETWORKPROTOCOL = 1
Any suggestion would be greatly appreciated.
Regards,
A.Chellam.How do you connect? Try specify the IP for the server/data source and see if
you
can connect.
-oj
http://www.rac4sql.net
"chellam" <chellammal@.touchtelindia.net> wrote in message
news:OphgNmxBEHA.3256@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I have installed MSDE 2000 server and i installed service pack
> 3a. The server machine has SQL Server 2000 client connectivity. When i
> connect from client machine to Server machine it is showing error.
> [Microsoft ] [ODBC SQL Server Driver] [TCP/IP sockets]
> SQL Server does not exists or access denied.
> I installed Servicepack 3a with out DISABLENETWORKPROTOCOL = 1
> Any suggestion would be greatly appreciated.
>
> Regards,
> A.Chellam.
>
>|||Verify on the MSDE machine using Server Network Utility what netlibs it is
listening on.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"chellam" <chellammal@.touchtelindia.net> wrote in message
news:OphgNmxBEHA.3256@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I have installed MSDE 2000 server and i installed service pack
> 3a. The server machine has SQL Server 2000 client connectivity. When i
> connect from client machine to Server machine it is showing error.
> [Microsoft ] [ODBC SQL Server Driver] [TCP/IP sockets]
> SQL Server does not exists or access denied.
> I installed Servicepack 3a with out DISABLENETWORKPROTOCOL = 1
> Any suggestion would be greatly appreciated.
>
> Regards,
> A.Chellam.
>
>

Problem in accessing the database(Sqlserver 2005) from client machine

Hi

We have upgraded sql server 2000 to sqlserver 2005 on the development server.

To access the database server, we installed the sql server management studio on the client PC with Admin Account. The .Net framework and sqlserver management studio were installed and I am able to access the database from the client PC using Admin Login. Then we logged off and tried to login as user account on the client PC. From the user account login we are unable to open the sql server management studio. The error is as follows

“The proper type library could not be found in system registry”

The client PC configuration : windows 2000 Professional ( sp4)

Regards,

K.S.Subba Rao

Hi K.S. Subba,

What is probably happening is the non-Admin user that you are logging on with does not have permissions to read the appropriate type library informations for SQL-specific COM components from registry. The fastest approach you can take to troubleshoot that is to use RegMon from http://www.microsoft.com/technet/sysinternals/SystemInformation/Regmon.mspx, find out which keys are "Access Denied" or "Not Found" and when logged as Admin, grant the permissions to those to the non-Admin user. As a lazy alternative you could try to grant permissions to the user to HKEY_LOCAL_MACHINE\SOFTWARE\Classes and all the subcomponents, but that might expose a security risk.

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

problem getting the most recent record for a range of clients

I have a fairly complex SP with 3 tables the idea being to display a Region, Client and Event
I want to show only the last event for each client

I have replaced my event table with a View which returns the TOP 1 but all I get is the most recent of all records
I want eg:
Region 1, Client 1, Most recent event
Region 1, Client 2, Most recent event
Region 1, Client 3, Most recent event

Not sure of the structure of your tables, but maybe you're looking for something like this?

SELECT RegionName, ClientName, EventName FROM Regions r INNER JOIN Clients c ON r.ClientID=c.ClientID INNER JOIN Events e ON c.ClientID=e.EventID WHERE EventDate=(SELECT MAX(EventDate) FROM Events WHERE ClientID=c.ClientID)

|||

Just the job Many thanks , I was not thinking right, this is a simplified version, unfortunatly I needed two MAX fields one for Date the other for EventID as the last ID may not be the most recent Date

exmple below gets all events with the most recent date then picks the most recent id from the most recent dates,

SELECT dbo.tblClients.FName, dbo.tblClients.SName, dbo.tblOCompEvents.CompEventID, dbo.tblOCompEvents.StartDate,
dbo.tblOCompEvents.IRef, dbo.tblClients.ClientID, dbo.tblOCompEvents.Status
FROM dbo.tblOCompEvents INNER JOIN
dbo.tblClients ON dbo.tblOCompEvents.ClientID = dbo.tblClients.ClientID
WHERE (dbo.tblOCompEvents.StartDate IN
(SELECT MAX(StartDate) AS sd
FROM dbo.tblOCompEvents AS tblOCompEvents_1
WHERE (ClientID = dbo.tblOCompEvents.ClientID))) AND (dbo.tblOCompEvents.CompEventID IN
(SELECT MAX(CompEventID) AS ceid
FROM dbo.tblOCompEvents AS tblOCompEvents_1
WHERE (ClientID = dbo.tblOCompEvents.ClientID))) AND (dbo.tblOCompEvents.Status = 25)
ORDER BY dbo.tblClients.ClientID

Problem Excel as OLAP Client with large volume of data

Dear all,

I am using Excel 2000 as my OLAP cube cleint. It's flexible and user friendly when I need to retrive data from cube. However, I am having problem when I need to retrive large volume of data from cube to the excel report (i.e: bank & finance report).

I tried link server but it doesn't make much different.

Can anyone tell me how to solve the problem, if I could only use excel or other license free application as my OLAP Client.

Thanks!

regards,
SamYou didn't mentioned about the problem you're having i.e., Performance related or query based.

If its performance based what is the current configuration of server and database information.

Problem displaying report services in client browsers

Hi all

I'm working on a project where we've developed a number of reports using SQL Reporting Services. Everything works fine on the developer PCs and when they cannect to the reporting services URL they can see all the reports listed and everything works fine. When the testers connect, they can see the basic reporting services homepage with the contents and properties tabs. But when they click on the contents tab nothing is displayed.

The tester pcs are locked down and the user profiles wouldnt have the same access rights as the developers who are all local admins on their PCs.

My question is what would prevent the reports from being listed? Is there an activex control that is possibly being blocked by the local security policy for the tester profiles? If this is the case is there anything that can be pre installed on the client pcs to get around this problem?

Any thoughts or assistance that anyone could provide would be grately approeciated

Thanks

Mark
Make sure that these users have the proper roles assigned for the report server. There is Browser, Content Manager (that can pretty much do everything), Report Builder, My Reports and Publisher. There is also a way to define custom roles if needed. The Report server manager can set up users, groups and role assignments.

problem creating view on table from linked server DB using IP

>>create a simply-named alias using Client Network Utility
This sounds like a great idea. May I ask where I locate the Client Network
Utility and how to create the simply-named alias?
"Aaron Bertrand [SQL Server MVP]" wrote:

> Another thing to reduce the complexity here, of having IP addresses
> hard-coded into your query, is to create a simply-named alias using Client
> Network Utility, and then refer to the alias instead of the IP address. N
ot
> that this makes it okay to use the view designer, but I think it is a bett
er
> approach overall. In addition to alleviating problems with 4-dot naming,
it
> also makes it much easier to update the system should that IP address
> change - you just change the alias definition instead of all the places yo
u
> manually referred to it in code.
>
>> This sounds like a great idea. May I ask where I locate the Client
> Network
> Utility
Start > Programs > Microsoft SQL Server >|||Thank you. So I went to the utility. I clicked on the Alias tab. Then I
clicked Add. In the Server alias box I entered the alias name which was
simultaneously showing up in the Connection Parameters Server Name. I
changed that to the IP address of the remote server. Then I selected TCP/IP
in the Network Libaries and clicked OK.
In the Linked Server (from Enterprise Manager), I added the Aliased remote
server using the same context as the link using the IP address. I was not
able to link to the Alias server. Do I need to remove the linked server tha
t
is using the IP address first? I am a little afraid of doing that because
I
already have my client app (VB.Net) working and can retrieve data from the
views (which use the IP address of the remote server).
Well, if anything, it is an interesting exercise.
Thanks for the help.
Rich
"Aaron Bertrand [SQL Server MVP]" wrote:

> Start > Programs > Microsoft SQL Server >
>
>|||Server alias: should be "myserver"
Server name: should "a.b.c.d"
When you add the linked server, use the name "myserver"
What does "not able to" mean? Did you get an error message? If so, what
was it?
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:B2E4C610-E3DB-4229-93BD-D0B277868000@.microsoft.com...
> Thank you. So I went to the utility. I clicked on the Alias tab. Then I
> clicked Add. In the Server alias box I entered the alias name which was
> simultaneously showing up in the Connection Parameters Server Name. I
> changed that to the IP address of the remote server. Then I selected
> TCP/IP
> in the Network Libaries and clicked OK.
> In the Linked Server (from Enterprise Manager), I added the Aliased remote
> server using the same context as the link using the IP address. I was not
> able to link to the Alias server. Do I need to remove the linked server
> that
> is using the IP address first? I am a little afraid of doing that
> because I
> already have my client app (VB.Net) working and can retrieve data from the
> views (which use the IP address of the remote server).
> Well, if anything, it is an interesting exercise.
> Thanks for the help.
> Rich
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||When I add the Aliased server I use the Alias name "myServer"
I use the same security context as the original linked server - the UID,
impersonate, and the password, then apply. The new linked server shows up i
n
Enterprise manager, but when I click on tables - about 30 seconds later I ge
t
the error message that either the server "myServer" does not exist or Access
denied. In Query analyzer I try select * from myserver.remotedb.dbo.table1
I get the error message the same error message
"Server does not exist or Access denied"
when I look in sysservers, however
Use Master
select * from sysservers
I can see "myServer" in the list of servers. My question now is if the
Alias is correct - correctly referring to my remote server, and am I using
the correct security context.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Server alias: should be "myserver"
> Server name: should "a.b.c.d"
> When you add the linked server, use the name "myserver"
> What does "not able to" mean? Did you get an error message? If so, what
> was it?
>
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:B2E4C610-E3DB-4229-93BD-D0B277868000@.microsoft.com...
>
>|||Not sure, but you may have to restart the SQL Server service for the alias
to be picked up and routable? Also, have you tried any other authentication
methods (e.g. not defining a local login and checking "be made using this
security context" and putting the remote uid/password in that dialog)?
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:CC0FF896-33EF-4F9E-B881-1867D785255C@.microsoft.com...
> When I add the Aliased server I use the Alias name "myServer"
> I use the same security context as the original linked server - the UID,
> impersonate, and the password, then apply. The new linked server shows up
> in
> Enterprise manager, but when I click on tables - about 30 seconds later I
> get
> the error message that either the server "myServer" does not exist or
> Access
> denied. In Query analyzer I try select * from
> myserver.remotedb.dbo.table1
> I get the error message the same error message
> "Server does not exist or Access denied"
> when I look in sysservers, however
> Use Master
> select * from sysservers
> I can see "myServer" in the list of servers. My question now is if the
> Alias is correct - correctly referring to my remote server, and am I using
> the correct security context.
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>

Problem creating Job from tsql

I'm attempting to create a job on a client's server using tsql over a VPN.
* I've created this procedure before using the same tsql and VPN
* The same tsql works presently on my server
* I'm not having any other problems on the server
* We're using NT auth, and my userid is sa
When I try to create a job from query analyzer, I get:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (se
nd()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
Any ideas?
Thanks!
GregSeems that 'sp_add_jobstep' is generating the error. Anyone have a similar
experience?
--
Greg C
"Greg C" <uce@.ftc.com> wrote in message news:QAxoc.88432$Dn1.44627@.fe2.texas
.rr.com...
I'm attempting to create a job on a client's server using tsql over a VPN.
* I've created this procedure before using the same tsql and VPN
* The same tsql works presently on my server
* I'm not having any other problems on the server
* We're using NT auth, and my userid is sa
When I try to create a job from query analyzer, I get:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (se
nd()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
Any ideas?
Thanks!
Greg

Problem connection to SQL Server with ODBC

What protocols are being used by the client and what is the
server listening on?
Are the client network utilities on the clients having the
connectivity problems?
Are you using an alias for the connection?
What version of MDAC on the clients?
From your description, it sounds like the app tries to
connect twice (which is fine) but that it's just not hitting
the right protocols. You can find information on how a
client runs through the protocols trying to connect in this
article:
INF: SQL Server Clients May Change Protocols When They Try
to Connect
http://support.microsoft.com/?id=328383
You can also find some general troubleshooting for the error
at:
INF: Potential Causes of the "SQL Server Does Not Exist or
Access Denied" Error Message
http://support.microsoft.com/?id=328306
-Sue
On Tue, 6 Apr 2004 10:12:37 +0100, "Ian Spanswick"
<ian.spanswick@.eps-hq.co.uk> wrote:

>Hi,
>Getting very frustrated by the following problem, using SQL Server 2000 (inc
>SP3a), using an application that connects to a DB via ODBC and the following
>error message comes up:
>Connection failed:
>SQLSTATE: '01000'
>SQL Server Error: 1703
>[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen
>(Connect()).
>Connection failed:
>SQLState: '08001'
>SQL Server Error:17
>[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist
>or access denied
>When setting up the DSN the test works fine as does query analyzer, pretty
>sure it's not the application I'm using as the SQL server connection window
>that comes up after the initial error (above) gives the same error message.
>Any ideas? Help is definitely needed before I tear my hair out!
>Thanks
>Ian
>
Thanks for the links, studying them now but have moved my database to
another server and things are going better! Still want to get to the bottom
of the problem.
Ian
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:i3i670lutthh68l5mqlgn479ng66ornom3@.4ax.com...
> What protocols are being used by the client and what is the
> server listening on?
> Are the client network utilities on the clients having the
> connectivity problems?
> Are you using an alias for the connection?
> What version of MDAC on the clients?
> From your description, it sounds like the app tries to
> connect twice (which is fine) but that it's just not hitting
> the right protocols. You can find information on how a
> client runs through the protocols trying to connect in this
> article:
> INF: SQL Server Clients May Change Protocols When They Try
> to Connect
> http://support.microsoft.com/?id=328383
> You can also find some general troubleshooting for the error
> at:
> INF: Potential Causes of the "SQL Server Does Not Exist or
> Access Denied" Error Message
> http://support.microsoft.com/?id=328306
> -Sue
> On Tue, 6 Apr 2004 10:12:37 +0100, "Ian Spanswick"
> <ian.spanswick@.eps-hq.co.uk> wrote:
(inc
following
exist
pretty
window
message.
>

Problem connecting to SS2K5 named instance using client tools

Hi,

The problem I have is this:

I have installed SQL Server 2005 Enterprise Edition on a W2K3 server that already has a SQL Server 2000 default instance installed. The installation appears to be a success. The problem is that having installed the client tools, and trying to connect, I get the following error message in a dialog:

"Cannot connect server SERVERNAME\INSTANCENAME
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating
Server/Instance Specified)(Microsoft SQL Server)"

The funny thing is that I can connect to the SQL Server 2000 default instance that is on that server.
So it's obviously not a connection problem per se at my end i.e. bad dns etc which might stop me resolving the server name.

Googling for this error text gives the following advice on various web sites:

1. Check that the remote server allows connections.

It does, as other colleagues in my team have successfully installed the tools and can connect with no problems.

2. Check that the SQL Browser service is running.

It is, as without this running, my colleagues would not be able to connect either.

3. Open SQL Server Configuration Manager. Select "SQL Server 2005 Network Configuration | Protocols for MSSQLSERVER" then enable the protocols you need.

The required protocol (TCP/IP) is enabled. Named pipes is also enabled, although I don't think that we're using that. And shared memory is also enabled by default.

4. Open the firewall and add exceptions for sqlbrowser.exe or UDP port
1434

There is no firewall running at either end of the connection and as mentioned I can connect to the SS2K default instance.

5. Make sure that the server machine is reachable.

I can connect to the SS2K default instance so it must be reachable. I can't see that I would need to change any configuration settings as my colleagues are using exactly the same default settings for their connections and they work fine.

The only thing that I have done differently is that I originally installed the client tools using someone else's privileged account as I didn't have admin rights on my box. The client tools didn't work, so I got admin rights to my box, uninstalled the client tools and then reinstalled them using my account. Still no luck. Not sure if something is still hanging around after the uninstallation that would cause me a problem.

To help, I've also filled out the following questionnaire:

[1] Client side:

> What is the connection string in you app or DSN?

There isn't one. I'm trying to connect from the SQL Server Management Studio | 'Connect to Server' dialog.

> If client fails to connect, what is the client error messages?

"Cannot connect server Y\Z
An error has occurred while establishing a connection to the server. When connecting to SQL Server
2005, this failure may be caused by the fact that under the default settings SQL Server does not
allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating
Server/Instance Specified)(Microsoft SQL Server)"

Is the client remote or local to the SQL server machine? Remote

Can you ping your server? YES

Can you telnet to your SQL Server? YES

What is your client database provider? [SNAC | MDAC | ADO.NET1.0 | ADO.NET2.0| other (please specify] N/A
What is your client application? SQL Management Studio
Is your client computer in the same domain as the Server computer? Different domains
What protocol the client enabled? [Shared Memory | TCPIP | Named Pipes]. All 3 protocols are enabled
Do you have aliases configured that match the server name portion of your connection string? No
Do you select force encryption on server and/or client? No

[2] Server side:

What is the MS SQL version? SQL Server 2005
What is the SKU of MS SQL? Enterprise
What is the SQL Server Protocol enabled? [Shared Memory | TCPIP | Named Pipes ]. All 3
Does the server start successfully? YES. Other people can connect to the same instance.
If SQL Server is a named instance, is the SQL browser enabled? YES
What is the account that the SQL Server is running under? Domain Account
Do you make firewall exception for your SQL server TCP port if you want connect remotely through TCP provider? N/A. Firewall not running.
Do you make firewall exception for SQL Browser UDP port 1434? N/A. Firewall not running.
What tool or Application are you using to connect to SQL Server SQL Server Management Studio

[3] Platform:

What is the OS version? Windows 2003 (Server side), Windows XP Pro Version 2002 SP 2
Do you have third party antivirus, anti-spareware software installed? Symantec AntiVirus

[4] Misc:

If you have certificate configuration issue: N/A

Has anybody else come across this problem, as I've run out of ideas.

Many thanks,

Hi,

I've fixed the problem.
One of my colleagues who can log onto the SS2005 named instance from his workplace came to my workplace today and found that he also couldn't connect from there. That ruled out my installation. So we played with SQLCMD trying to connect and found that we could if we specified the TCP port (1776). So then it was just a question of working out
what the syntax was to specify the port in the Connection dialog. Turns out that server-name\,1776 works and allows us to connect.

Thanks.