DSN Connection to Remote SQL Server

Hi,

I've worked with SQL Server plenty but never in this specific manner. Hoping someone can shed some light.

I have a webform on web server A that needs to talk to a SQL server database that web server B accesses via its web application. I do not have
any privelidged access to server B or the sql server it uses (no FTP to webserver, no remote access via Enterprise Manager to the SQL server). The Admin of Server B told me that I can use a DSN connection to talk to the SQL server database that the web app on server B uses. He emailed me the names of the SQL database tables AND a copy of the web.config from server B which shows the DSN implemented like this:

<appSettings>
<add key="dsn" value="data source=sql3.domainname.net;database=dbname;User Id=dbuserid;password=dbpw;" />
<add key="default_language" value="en" />
<add key="error_email_to" value="someone@.domainname.com" />
<add key="baseHref" value="http://www.mysite.com/" />
<add key="website_name" value="$$$$$$$$$$$$$$$$$" />
</appSettings>

I was planning to run my webforms on Server A where I do have FTP, Control Panel, etc.
So, I wrote this code into my VB.NET codebehind file.

Dim oODBCConnectionAs OdbcConnection
Dim sConnStringAsString = _
"Driver={SQL Server};" & _
"Server=sql3.domainname.net;" & _
"Database=dbname;" & _
"Uid=dbuserid;" & _
"Pwd=dbpw"

oODBCConnection =New Odbc.OdbcConnection(sConnString)
oODBCConnection.Open()
... commands to access data and load something into a control on a webform
oODBCConnection.Close()

When I attempt to run the webform in my browser from Server A. I'm getting this error:
ERROR [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
ERROR [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
The error is EXACTLY the same whether I place the appSettings section in server A's web.config or leave them out.

I have obvious questions about the error like...
Might sql3.domainname.net simply be refusing communication requests that don't come from the Server B domain?
Might the sql3.domainname.net be setup so that I need to use another protocol (not tcp/ip)?
Might my code need to be changed to support this type of connection (i.e. I didn't code it right)?

I spent some time readinghttp://support.microsoft.com/kb/328306/en-us but I'm at a loss as to what the best course is to take for debugging this.
Thanks in advance for any insight you can offer.

Stewart

Hi,

I think you should get the remote server's ip address and user account to creat a local DSN and call the DSN in your application. Another easy solution is to use SqlConnection rather then ODBCConnection and modify your connection string and make it look like "DataSource=IP address;Initial Catelog=DBName;User Id=;Password="

Thanks.

Dropped connections, but open on the SQL Server...

I have a frustrating issue that hopefully someone can help with.
I have a few remote users that connect to the sql server through 1433 and
sql authentication (the ISA firewall only allows their IP addresses to
connect). They connect through an ODBC connection with no Idle timeout.
These users are getting their connections closed but I look at the EM and
the connection is still open. If I have them connect via VPN first,
everything works fine - the connection is never closed. Do you know why a
standard ODBC connection would close itself only if it's not behind a VPN?
I can't figure it out for the life of me!! There must be some timeout
parameter somewhere that I have set, but I can't find it...
Any ideas would be greatly appreciated!
Regards,
Brett Wickard
Hi Brett,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to close ODBC
connection when the user terminate the connection. If I have misunderstood
your concern, please feel free to point it out.
First of all, please use sp_who in the Query Analyzer to make sure the
connnection from these remote customer remain alive.
Secondly, how does your customer connect to SQL Server? If you utilize ADO
in your VB application to access the SQL Server, please try the following
sample code.
Dim cn As New ADODB.connection
Dim cmd As New ADODB.Command
cn.ConnectionTimeout = 45
cmd.CommandTimeout = 15
cn.close
For more information, please refer to Microsoft SQL Server Books Online
with the keywords 'ADO' and 'connectiontimeout' or 'commandtimeout' as the
search topics.
Last but not the least, the following KB describes how to define the
orphaned connection and set OLE DB Provider for ODBC's timeout settings.
INFO: OLE DB Session Pooling Timeout Configuration
http://support.microsoft.com/kb/Q237977
INF: How to Troubleshoot Orphaned Connections in SQL Server
http://support.microsoft.com/kb/Q137983
INFO: Frequently Asked Questions About ODBC Connection Pooling
http://support.microsoft.com/kb/Q169470
Resource Pooling
http://msdn.microsoft.com/library/de...us/oledb/htm/o
ledbresourcepooling.asp
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Dropped connections, but open on the SQL Server...

I have a frustrating issue that hopefully someone can help with.
I have a few remote users that connect to the sql server through 1433 and
sql authentication (the ISA firewall only allows their IP addresses to
connect). They connect through an ODBC connection with no Idle timeout.
These users are getting their connections closed but I look at the EM and
the connection is still open. If I have them connect via VPN first,
everything works fine - the connection is never closed. Do you know why a
standard ODBC connection would close itself only if it's not behind a VPN?
I can't figure it out for the life of me!! There must be some timeout
parameter somewhere that I have set, but I can't find it...
Any ideas would be greatly appreciated!
Regards,
Brett WickardHi Brett,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to close ODBC
connection when the user terminate the connection. If I have misunderstood
your concern, please feel free to point it out.
First of all, please use sp_who in the Query Analyzer to make sure the
connnection from these remote customer remain alive.
Secondly, how does your customer connect to SQL Server? If you utilize ADO
in your VB application to access the SQL Server, please try the following
sample code.
--
Dim cn As New ADODB.connection
Dim cmd As New ADODB.Command
cn.ConnectionTimeout = 45
cmd.CommandTimeout = 15
cn.close
--
For more information, please refer to Microsoft SQL Server Books Online
with the keywords 'ADO' and 'connectiontimeout' or 'commandtimeout' as the
search topics.
Last but not the least, the following KB describes how to define the
orphaned connection and set OLE DB Provider for ODBC's timeout settings.
INFO: OLE DB Session Pooling Timeout Configuration
http://support.microsoft.com/kb/Q237977
INF: How to Troubleshoot Orphaned Connections in SQL Server
http://support.microsoft.com/kb/Q137983
INFO: Frequently Asked Questions About ODBC Connection Pooling
http://support.microsoft.com/kb/Q169470
Resource Pooling
http://msdn.microsoft.com/library/d...-us/oledb/htm/o
ledbresourcepooling.asp
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Drop Remote Servers

Hi,
I have a database server and my predecessor created replication and
then dropped it. Now I am stuck with some servers under Remote Servers.
When I try to delete them I get an error message: 20583 cannot drop
server because it is used as a subscriber. Any hints on how I can drop
these Remote Servers?
Thank you,
hjHi
You may want to look at sp_helpsubscription and sp_dropsubscription. You may
want to check out sp_removedbreplication.
John
"Hitesh" wrote:

> Hi,
> I have a database server and my predecessor created replication and
> then dropped it. Now I am stuck with some servers under Remote Servers.
> When I try to delete them I get an error message: 20583 cannot drop
> server because it is used as a subscriber. Any hints on how I can drop
> these Remote Servers?
> Thank you,
> hj
>

Drop Remote Servers

Hi,
I have a database server and my predecessor created replication and
then dropped it. Now I am stuck with some servers under Remote Servers.
When I try to delete them I get an error message: 20583 cannot drop
server because it is used as a subscriber. Any hints on how I can drop
these Remote Servers?
Thank you,
hjHi
You may want to look at sp_helpsubscription and sp_dropsubscription. You may
want to check out sp_removedbreplication.
John
"Hitesh" wrote:
> Hi,
> I have a database server and my predecessor created replication and
> then dropped it. Now I am stuck with some servers under Remote Servers.
> When I try to delete them I get an error message: 20583 cannot drop
> server because it is used as a subscriber. Any hints on how I can drop
> these Remote Servers?
> Thank you,
> hj
>

Drop Remote Servers

Hi,
I have a database server and my predecessor created replication and
then dropped it. Now I am stuck with some servers under Remote Servers.
When I try to delete them I get an error message: 20583 cannot drop
server because it is used as a subscriber. Any hints on how I can drop
these Remote Servers?
Thank you,
hjYou have to first remove the subscriptions.
Check in Books on Line about publishers and subscribers.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Hitesh" <hitesh287@.gmail.com> wrote in message
news:1155056854.677819.318060@.m73g2000cwd.googlegroups.com...
> Hi,
> I have a database server and my predecessor created replication and
> then dropped it. Now I am stuck with some servers under Remote Servers.
> When I try to delete them I get an error message: 20583 cannot drop
> server because it is used as a subscriber. Any hints on how I can drop
> these Remote Servers?
> Thank you,
> hj
>

Driving me insane with remote connections in SQL 2005

I have developed an application that utilize SQL server 2005. The application is a clickonce application and launched from the IIS. The problem is that when im trying to connect from outside the company i get the Error that the server does not accept remote connections.

I have used the surface manager and setup the protocol to accept remote connections. Nothing happens.

I have changed the IIS to use the configuration screens under the .NET configuration in IIS. Nothing helps there either.

I have open the ports 445 ,1433, 1434

Anyone knows what i could do to get the connections working on the %%%%%%

I'm moving your question to the data access forum where you should get an answer.|||

Can you post the exact error msg here?
Is your SQL instance the default instance? Can you check whether it's listening on NP and/or TCP? and TCP port number? You need to run SQL Browser. I assume you opened port UDP 1434, not TCP 1434.

or at best, collect information according to
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362498&SiteID=1

Thanks,

|||Have a look at:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2464749&SiteID=1

Mortaza Doulaty
http://www.mrt-web.com

Driving Crazy

hi,
There is something i really don't understand.
There is a remote server shows under Security --> Remote Servers
and I just cannot delete it.
I run sp_dropremotelogin -->
it said "There are no remote users mapped to any local user from remote
server 'repl_distributor'
I run sp_dropserver 'repl_distributor' -->
it said "There are still remote logins for the server 'repl_distributor'
? how am I able to remove that remote login?
Thanks so much
EdThe name suggests that it is a remote server created by replication, which
facilitates communication between the Distributor and Publisher. If you need
to remove the replication and the remote server, use the replication menu
under Tools in EM and click on Disable Publishing and Distributing.
Anith

drillthrough problems with remote reports

After spending some time searching the forums for other people with these same issues, I've come up empty handed and decided it was time to make a post. I'm using the VS2005 WebForms ReportViewer object to embed remote (i.e. not using rdlc) reports on an asp.net page. When the page is first rendered, I have code to set the ServerReport.ReportPath property to the location of a parent-level report on my report server. Each record in this report has a drillthrough link to another server report, and each record in this detail-level report has ANOTHER drillthrough link to a third report that is an even more detailed view of the data. So in short, I have a set of drillthrough reports that can go three levels deep and the top-level report is rendered by default when the page is first viewed.

The behavior that I would expect is that when I click on the drillthrough links in the first or second report, the ReportViewer would incur a postback and automatically do the plumbing to render the appropriate drillthrough report with the proper parameters passed in by the parent report. I would also expect the "back" button in the ReportViewer toolbar to correctly navigate back up the tree to the top-level parent report without any additional programming.

The behavior that I'm ACTUALLY seeing is that the drillthrough works fine from TopLevelReport to MidLevelReport works fine, but when I click to drillthrough from MidLevelReport to BottomLevelReport, the ReportViewer re-renders MidLevelReport with different parameters, as if I had gone back to TopLevelReport and clicked on a different record. However, if at this point I click on a drillthrough link in the new rendering of MidLevelReport, it correctly navigates to BottomLevelReport.

Also, the back button functionality is unpredictable; sometimes I receive this exception when clicking the back button from MidLevelReport:

System.InvalidOperationException: Back call without drillthrough report at Microsoft.Reporting.WebForms.ReportViewer.Toolbar_Back(Object sender, EventArgs e) at Microsoft.Reporting.WebForms.ToolbarControl.OnBack(Object sender, EventArgs e) at Microsoft.Reporting.WebForms.BackGroup.BackButton_Click(Object sender, EventArgs e) at Microsoft.Reporting.WebForms.ScriptSwitchImage.EnabledImage_Click(Object sender, EventArgs e) at Microsoft.Reporting.WebForms.HoverImage.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

When clicking the back button on BottomLevelReport, instead of throwing the above exception it correctly navigates back to MidLevelReport, but then the back button is disabled and I can't go back up to TopLevelReport.

I've experimented with hooking into the ReportViewer.DrillThrough and ReportViewer.Back events to see if maybe I'm just expecting the ReportViewer to handle some under-the-hood plumbing that I'm supposed to code myself, such as updating the value of ServerReport.ReportPath or calling ServerReport.Refresh. I won't go into lengthy details of all the combinations of coding I've tried, but all of them introduced some sort of incorrect navigation behavior or outright exceptions. What I really need is for somebody experienced with using ReportViewer to display remote drillthrough reports more than two levels deep to tell me exactly what I need to do in my DrillThrough and/or Back event handlers in order to make the ReportViewer behave in the expected way.

Thanks in advance to anybody who replies; I've been stuck on this problem for a couple of days now and I'm starting to consider abandoning the idea of using ReportViewer for this project.

vigg:

After spending some time searching the forums for other people with these same issues, I've come up empty handed and decided it was time to make a post. I'm using the VS2005 WebForms ReportViewer object to embed remote (i.e. not using rdlc) reports on an asp.net page. When the page is first rendered, I have code to set the ServerReport.ReportPath property to the location of a parent-level report on my report server. Each record in this report has a drillthrough link to another server report, and each record in this detail-level report has ANOTHER drillthrough link to a third report that is an even more detailed view of the data. So in short, I have a set of drillthrough reports that can go three levels deep and the top-level report is rendered by default when the page is first viewed.

The behavior that I would expect is that when I click on the drillthrough links in the first or second report, the ReportViewer would incur a postback and automatically do the plumbing to render the appropriate drillthrough report with the proper parameters passed in by the parent report. I would also expect the "back" button in the ReportViewer toolbar to correctly navigate back up the tree to the top-level parent report without any additional programming.

The behavior that I'm ACTUALLY seeing is that the drillthrough works fine from TopLevelReport to MidLevelReport works fine, but when I click to drillthrough from MidLevelReport to BottomLevelReport, the ReportViewer re-renders MidLevelReport with different parameters, as if I had gone back to TopLevelReport and clicked on a different record. However, if at this point I click on a drillthrough link in the new rendering of MidLevelReport, it correctly navigates to BottomLevelReport.

Also, the back button functionality is unpredictable; sometimes I receive this exception when clicking the back button from MidLevelReport:

System.InvalidOperationException: Back call without drillthrough report at Microsoft.Reporting.WebForms.ReportViewer.Toolbar_Back(Object sender, EventArgs e) at Microsoft.Reporting.WebForms.ToolbarControl.OnBack(Object sender, EventArgs e) at Microsoft.Reporting.WebForms.BackGroup.BackButton_Click(Object sender, EventArgs e) at Microsoft.Reporting.WebForms.ScriptSwitchImage.EnabledImage_Click(Object sender, EventArgs e) at Microsoft.Reporting.WebForms.HoverImage.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

When clicking the back button on BottomLevelReport, instead of throwing the above exception it correctly navigates back to MidLevelReport, but then the back button is disabled and I can't go back up to TopLevelReport.

I've experimented with hooking into the ReportViewer.DrillThrough and ReportViewer.Back events to see if maybe I'm just expecting the ReportViewer to handle some under-the-hood plumbing that I'm supposed to code myself, such as updating the value of ServerReport.ReportPath or calling ServerReport.Refresh. I won't go into lengthy details of all the combinations of coding I've tried, but all of them introduced some sort of incorrect navigation behavior or outright exceptions. What I really need is for somebody experienced with using ReportViewer to display remote drillthrough reports more than two levels deep to tell me exactly what I need to do in my DrillThrough and/or Back event handlers in order to make the ReportViewer behave in the expected way.

Thanks in advance to anybody who replies; I've been stuck on this problem for a couple of days now and I'm starting to consider abandoning the idea of using ReportViewer for this project.

I am facing the exact same issues and am in the same boat. Depending on whether these multilevel drillthrough server reports work properly, I might abandon or use the Reportviewer for this project. its very crucial.

I am also having another issue. In my "mid level drill through report", the Dundas chart that i am using isnt showing either. It shows when its not a drillthrough but the top level report. But not in the drillthrough mode. HELP PLEASE !!

|||

I try this before and it works perfectly but i don't get your problem exactly so i advice you to check the examples in this link

http://www.gotreportviewer.com/

|||

Same issue here, with a variation...

I've got a report that takes a parameter, and another similar report without a parameter. Both have drillthroughs to detail reports. On the report without the parameter, the drillthrough works just fine. On the report *with* the parameter, clicking on the drillthrough link simply reloads the page. It does a postback, the event targets appear to be set, but the drillthrough event does not get fired. I discovered that the very fact that I am setting the parameters (eg, ReportViewer1.ServerReport.SetParameters(_params)) is causing the drillthrough event to not fire--I set the parameters on one of the non-parameterized reports to an empty list of parameters, and behold, the drillthrough event is no longer fired.

Is this a bug with the report viewer, or is there another way to set parameters that doesn't break the drillthrough event? I've tried setting the callback both before and after the parameters are set, so I'm really out of ideas.