ADP: Can't use stored procedure on remote SQL server

[Marcus]

I'm happily using a remote SQL server over the internet using Sylvain
Lafontaine's suggestion:

"use a comma to specify the port number as in a.b.c.d,1433"

Unfortunatelly I have noticed that stored procedures have some problems,
that is
they don't execute at all when launched remotely... is there a particular
reason, in your opinion, why this happens? While in LAN, they work
perfectly....

The error is:


"Run-time error '2812' Could not find stored procedure 'SPInc'.


here's the offending code:
***********************

spstring = "exec SPInc '" & _
fGDataFine & _
"' , '" & _
fGDataInizio & _
"'," & _
Me.Anno & "," & _
Me.Mese & "," & _
AnnoPrec & "," & _
MesePrec & "," & _
spOrderBy


Me.RecordSource = spstring
**************************

I

 

[bcap]

You might try qualifying the SP name with the schema name e.g.

exec dbo.SPInc

 

[Marcus]

No.. same error....

You might try qualifying the SP name with the schema name e.g.

exec dbo.SPInc

 

[a a r o n . k e m p f]

do you have permissions to the sproc?

 

[Marcus]

Yes, as I've said it works perfectly when in LAN.... I'm wondering if some
other ports need to be accessed from the Internet or if some credentials are
passed automatically while in the Domain....
The strange is that everything works (SQL Queries, etc)


message
do you have permissions to the sproc?

 

[a a r o n . k e m p f]

are you using a different port?

Do you need to be?

try removing the port

 

[Sylvain Lafontaine]

No other port need to be open. I suppose that you might have some
permission issue here or that ADP have a problem opening some background
connection if you hare using Windows authentification over the VPN or the
WAN.

If you are using a Windows account, the first thing to try would be using a
SQL-Server login account.

Check also the owner of the SPInc stored procedure. Adding dbo. before
won't work at all if the owner is another account than dbo. What do you see
when you take a look at the Views/SP/Functions tab on the database window?

You should also take a look with the SQL-Server Profiler in order to see
more exactly what could happen here.

 

[bcap]

As Sylvain said, you need to use the *correct* schema prefix. dbo is only
an example, which I gave because it's the default. It's not necessarily the
one you should be using, you need to check the object.

 

[Marcus]

Yes, I'm using Windows Integrated security.... accessing the DB over the
Internet, surely doesn't gives any credentials....
Strange is that other queries and access to tables is enabled...
BTW in the database window, all the stored procedures are followed by
(xxxxUser) that id a SQL server username I have created... is the owner?
I'll post tomorrow what happens...

 

[Marcus]

I'm using a port only when accessing over the internet.


message
are you using a different port?

Do you need to be?

try removing the port

 

[Sylvain Lafontaine]

With ADP, I found by experience that's not a good idea to have anything else
then dbo as the owner of everything. Not only I use dbo everywhere but I
also take the precaution of setting the Record Source Qualifier property
(under the Data tab) of all forms to dbo.

If you don't mention the owner when creating a new stored procedure, view,
table or function; it get associated with the current user account. This is
probably what happening to you.

 

[Marcus]

You've got it right! I had to change the owner and now it works!

Thanks!!

 

[Sylvain Lafontaine]

Don't forget to set the Record Source Qualifier property of all forms under
the Data tab.

 

[Marcus]

It defaults to dbo.

Didn't know that by default stored proc. are created with creators' name...

BTW I'm wondering how Access is passing credential to the Windows/SQL server
over the Internet, through the firewall.... only the SQL server port
(remapped) is open..... can Access pass Windows credentials in such a a way?

As always, Sylvain, you are great!

 

[Sylvain Lafontaine]

For SQL-Server login account, the information is passed in clear. For
Windows authentification, the machine must already be previously logged
(when going over the Internet but not on a LAN/VPN).

 

[Tony Toews [MVP]]

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam

For SQL-Server login account, the information is passed in clear.

Oh really!?!? I didn't know that. My excuse though is that I've
hardly used SQL Server.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Sylvain Lafontaine]

For SQL-Server login account, the information is passed in clear.

Oh really!?!? I didn't know that. My excuse though is that I've
hardly used SQL Server.

Well, there's also the possibility of using encryption by installing a
certificate on SQL-Server and using SSL. However, it's an all or nothing
thing: either all connections to the server use encryption or either none of
them.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

 

[Tony Toews [MVP]]

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam

Well, there's also the possibility of using encryption by installing a
certificate on SQL-Server and using SSL. However, it's an all or nothing
thing: either all connections to the server use encryption or either none of
them.

Or using a VPN. Or so I would presume.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Sylvain Lafontaine]

Yes, the VPN will protect the connection but only over the WAN. For any
machine connected to the VPN, either remotely or locally on the LAN, there
shouldn't be any protection for the connection between two machines.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

 

[a a r o n . k e m p f]

yes... the resolution of course is to use 'Windows Authentication' and
to understand the INs and OUTs of double-hop authentication