Extra parameters being added onto SP call with ADO?

We are running into problems on our development environment with extra
parameters being added on to a stored procedure call. Instead of the
expected stored procedure call, the trace shows something like this:

declare @.P1 int
set @.P1=NULL
<<expected stored procedure call>> , @.P1 output, <<repeat of first
three sp parms>>
select @.P1

The developer has checked the code, and I have checked the SP - both
seem to match what is in production (which works fine). The databases
are on the same server, and the apps are running on seperate web
servers.

I'm guessing this may be some sort of configuration issue with ADO,
SQL, or something else - has anyone run into something similar to
this? Thanks!

DaveSo, is the sp running at all when called with that additional parameter? It
should fail if that parameter (the one you think is additional) is not
declared in the sp.

What happens if you paste the output from Profiler into Query Analyzer and
run it?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm

"Dokter Z" <dzahn@.execpc.com> wrote in message
news:7e6a5a6.0402250644.4ef950eb@.posting.google.co m...
We are running into problems on our development environment with extra
parameters being added on to a stored procedure call. Instead of the
expected stored procedure call, the trace shows something like this:

declare @.P1 int
set @.P1=NULL
<<expected stored procedure call>> , @.P1 output, <<repeat of first
three sp parms>>
select @.P1

The developer has checked the code, and I have checked the SP - both
seem to match what is in production (which works fine). The databases
are on the same server, and the apps are running on seperate web
servers.

I'm guessing this may be some sort of configuration issue with ADO,
SQL, or something else - has anyone run into something similar to
this? Thanks!

Dave|||Vyas -

Thanks for the response. Actually, we have discovered that the initial
problem was caused by

1) Some "sub-optimal" Paramaters.Refresh code
2) A stored procedure call issued by user aaaaa ended up calling
bbbbb.stored_proc_name instead of dbo.stored_proc_name. I'm currently
researching that issue...

Dave

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

external access denied to update a config file

Hello,

I'm having an issue with a CLR Stored procedure. Everything works great in a 32 bit environment, I have a CLR SP that updates an xml file stored on a local drive. When I execute the Stored proc it does go and update what I want it to in a 32 bit system. When I run the CLR SP on a 64 bit cluster, I seem to have give the "everyone" group write permissions to my G: drive (which is where the file is located that I'm updating). What security context is this SP running under? I thought it would be either under the SQL Service account (which is a domain user in the local administrators group) or what I'm logged in as when I run it from Management Studio (which is a domain admin, also in the local administrators group). If I have given the local administrators group "Full Control" access to the G: drive, why isn't this enough? Why do I have to give the Everyone group write access?

The security context seems odd to me, it seems like it's not running as either one of those 2 users I mentioned, because if it was, then it should be able to update the xml file.

Any help appreciated. Here's the error I'm getting:

Msg 6522, Level 16, State 1, Procedure usp_XMLWriter, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'usp_XMLWriter':

System.UnauthorizedAccessException: Access to the path 'g:\ssisPackages\BuildCalendar\andy.dtsConfig' is denied.

System.UnauthorizedAccessException:

at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)

at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)

at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boolean bFromProxy)

at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share)

at System.Xml.XmlTextWriter..ctor(String filename, Encoding encoding)

at System.Xml.XmlDocument.Save(String filename)

at XMLWriter.StoredProcedures.usp_XMLWriter(String xmlDocPath, String xmlNodePath, String xmlInnerText)

.

I found out that I'm able to get this to work if I put the SQL Service User account directly into the security of the G: Drive. For some reason, even though the user is in the Local Administrators group, it doesn't seem to pick up the fact that my user in is in the group, and therefore won't let him update a file in the folder.

Does anyone know of any issues in a 64 bit cluster where a CLR Stored proc isn't able to decipher the users in a local Group?

|||This turned out to be a cluster issue. We weren't doing permissions through the cluster, we were doing permissions to the file shares through each node, which is a mistake in a cluster.

Express Edition, Reporting Services, and Licensing

Hello,

From reading the licensing considerations page [1], my understanding is that if my environment is 2 servers - an IIS Server and a separate SQL Server Std - I will need a 2nd license of SQL Server to run Reporting Services on my IIS Server? Is this correct? If so, would a license of SQL Server Express Edition w/Advanced Services satisfy the second license requirement, or would I need a license to match the database server?

Essentially, what is the recommended guidance for Reporting Services and Web Applications? It's a known best practice that, for performance, run SQL Server on a dedicated machine separate from IIS. Is it recommended that the database server also serve the reports?

[1] http://www.microsoft.com/sql/howtobuy/howtolicensers.mspx

Thanks,

The definitive answer for licensing issues is to call the licensing folks at:

Licensing –VL Contact
(800) 426-9400

|||

Hi,

"Is it recommended that the database server also serve the reports?"

there is no no yes or no for this, cause this depends on your workload and the amount of licences you want to spend on.

For the first qestions you ask, the scenario mentioned is not possible as you are only allowed to query and report data from the local SQL Server database.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Jens K. Suessmeyer wrote:

"Is it recommended that the database server also serve the reports?"

there is no no yes or no for this, cause this depends on your workload and the amount of licences you want to spend on.

Jens,

Thanks for your reply. I take it by workload, you are referring to combined/overall workload, not just Reporting Services vs. Transactional/Operational, correct? Obviously, that would needed to be weighed into the decision.

Thanks,|||Exactly.

Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Just a point from your initial question...

You can not use Express Advanced to bypass the licensing for SQL Standard Edition. Every person or service connecting to Standard Edition must be properly licensed either by CAL or Proc License. Using Reporting Services from Express Advanced doesn't change the licensing requirements for Standard.

Mike

|||

Mike Wachal - MSFT wrote:

Just a point from your initial question...

You can not use Express Advanced to bypass the licensing for SQL Standard Edition. Every person or service connecting to Standard Edition must be properly licensed either by CAL or Proc License. Using Reporting Services from Express Advanced doesn't change the licensing requirements for Standard.

Mike

Mike,

Thanks for your reply. I'm going to contact licensing just to be safe, but that makes sense.

Thanks,

Express Database Security

Could somebody explain to me how does database security in the SQL Server Express environment works? It would seem that there has to be some level of "interplay" between the user's basic level of authority in Windows security on the machine (e.g., "Adminstrator" vs. "User"), the implications that that has on their level of access to the hard drive location where the database file is stored, and how security is configured within the database engine.

hi,

kschlap wrote:

Could somebody explain to me how does database security in the SQL Server Express environment works? It would seem that there has to be some level of "interplay" between the user's basic level of authority in Windows security on the machine (e.g., "Adminstrator" vs. "User"), the implications that that has on their level of access to the hard drive location where the database file is stored, and how security is configured within the database engine.

first of all, the engine runs under the Windows account it has been set to, like Network Service, LocalSystem (please do not), a local account or a domain account..

usually that account related NTFS permissions are validated for OS specific tasks like creating databases, performing backup and restore operations...

interactive users do usually not have to care about NTFS related permissions as they are managed at the account running the service level..

but SQL Server obviously has a security policy... before connecting interactive users, they are checked, both for integrated security and standard SQL Server authentication, agains the internal managed "list" of granted logins...

for integrated security, a round trip to the domain controller is performed, in order to get the interactive account's SID to be checked against that list, where, for standard SQL Server authenticated connections, user's name and password credentials are required to be checked against the registered standard SQL Server logins..

once that check is fulfilled, the interactive user is granted connection to the SQL Server instance..

by default, on pre Vista operating systems, local administrators are granted connection permissions as a group login is "generated" like BUILTIN\Administrators, granting them membership to the sys_admins server role.. so all local admins inherit full permissions on that instance...

another local group is granted connection, BUILTIN\Users, but no particular permissions are granted, nor is it made member of "powerfull" server's roles..

the second phase is related to database access... all logins but sysadmin members must be granted database access as well, which resolves creating database users related to server's logins..

with no presence of a related database user, a login is not granted permissions to access databases (if not member of sysadmins server role)..

additional granularity is then obtained providing permissions at object lever (for SELECT, etc as long as EXECUTE...) and even deeper at the column level for tables and views..

at the end of the game, as long as the interactive user has been granted permissions at SQL Server level, you do not have to care about NTFS permissions, as it's the account running SQL Server that will be checked.. so you have to provide NTFS permissions for that account..

a "minor" difference in this path regards attaching already detached databases, where SQL Server resets ACL on the detached files and you, as interactive user, have to be granted permissions at the NTFS level on that files before beeing able to reattach the database..

regards|||Thank you very much.
-Kyle

Exposing data

Hi,
I'm about to enter some new ground. I have a background of web development (
ASP) and SQL Server (network environment only). We have a new ASP.NET applic
ation running a SQL DB. This application is to be put out on the net.
Our network admin wants to buy another SQL licence and have the SQL server s
it outside of our network firewall. I'm more inclined to have the SQL server
inside some sort of DMZ (a topic new to me) and open a port for data access
.
Before I take this argument to the network admin I want to arm myself with t
he necessary knowledge. I guess I'm looking for articles/URLs/tutorials whic
h will teach me how to expose a SQL DB on our network to the internet withou
t risking the secuirty of o
ur network or the SQL box itself.
many thanks
JayGenerally for web apps, the exposure works like this:
One port (or maybe two) on the firewall is open for HTTP (80, and perhaps
443 for SSL).
The web server, BEHIND THE FIREWALL, can talk to the database server. Some
admins even go so far as to put a second firewall between the web and
database servers (which I think is generally overkill). No SQL Server ports
should ever be open on the firewall. And no SQL Server should ever be in a
DMZ! That's just asking for trouble...
Anyway, here's a link with some good information about how to secure a web
app using SQL Server:
http://msdn.microsoft.com/library/d...-us/dnnetsec/ht
ml/openhack.asp
"Jay" <Jay@.wdc.com> wrote in message
news:5D4E06B3-1BC0-4A9B-9215-2C20BC72379D@.microsoft.com...
> Hi,
> I'm about to enter some new ground. I have a background of web development
(ASP) and SQL Server (network environment only). We have a new ASP.NET
application running a SQL DB. This application is to be put out on the net.
> Our network admin wants to buy another SQL licence and have the SQL server
sit outside of our network firewall. I'm more inclined to have the SQL
server inside some sort of DMZ (a topic new to me) and open a port for data
access.
> Before I take this argument to the network admin I want to arm myself with
the necessary knowledge. I guess I'm looking for articles/URLs/tutorials
which will teach me how to expose a SQL DB on our network to the internet
without risking the secuirty of our network or the SQL box itself.
> many thanks
> Jay|||This would not be recommended. SQL should be protected by a firewall.
Consider using ISA server 2000 and publishing SQL to the internet.
1433 is one of the highest scanned ports by hackers.
http://www.microsoft.com/technet/pr...n/sp3sec02.mspx
#XSLTsection126121120120
287932 INF: TCP Ports Needed for Communication to SQL Server Through a
Firewall
http://support.microsoft.com/?id=287932
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Thanks Adam,
Priniting it now... will digest it after my first coffee of the morning.
Jay