Showing posts with label lan. Show all posts

Tuesday, March 27, 2012

accessing from T-SQL a database on another DBMS

tHi
I want to build a trigger to modify data in a table in a database that is
running on another Database Engine in may LAN. I don' t know if that is
possible. If it is, how will I make the connection to that database?
Thanks in advance - WaldoYou would have to set up the other instance as a "linked server". But what you want to do requires a
distributed transaction (with DTC running and all that jazz) so I would re-think the approach if
possible.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"wvandenbroeck" <wvandenbroeck@.discussions.microsoft.com> wrote in message
news:2A416FD1-02F9-427D-9748-7F2CE4E08CDD@.microsoft.com...
> tHi
> I want to build a trigger to modify data in a table in a database that is
> running on another Database Engine in may LAN. I don' t know if that is
> possible. If it is, how will I make the connection to that database?
> Thanks in advance - Waldo|||I concur. Consider some form of asynchronous mechanism where by you put the
requisite information in a queing table and pull from the other db engine to
do the updates.
You can get DTC to do what you need however, but it is often a PITA. :)
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eh02e1bLIHA.4684@.TK2MSFTNGP06.phx.gbl...
> You would have to set up the other instance as a "linked server". But what
> you want to do requires a distributed transaction (with DTC running and
> all that jazz) so I would re-think the approach if possible.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "wvandenbroeck" <wvandenbroeck@.discussions.microsoft.com> wrote in message
> news:2A416FD1-02F9-427D-9748-7F2CE4E08CDD@.microsoft.com...
>> tHi
>> I want to build a trigger to modify data in a table in a database that is
>> running on another Database Engine in may LAN. I don' t know if that is
>> possible. If it is, how will I make the connection to that database?
>> Thanks in advance - Waldo
>

accessing from T-SQL a database on another DBMS

tHi
I want to build a trigger to modify data in a table in a database that is
running on another Database Engine in may LAN. I don' t know if that is
possible. If it is, how will I make the connection to that database?
Thanks in advance - Waldo
I concur. Consider some form of asynchronous mechanism where by you put the
requisite information in a queing table and pull from the other db engine to
do the updates.
You can get DTC to do what you need however, but it is often a PITA.

Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eh02e1bLIHA.4684@.TK2MSFTNGP06.phx.gbl...
> You would have to set up the other instance as a "linked server". But what
> you want to do requires a distributed transaction (with DTC running and
> all that jazz) so I would re-think the approach if possible.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "wvandenbroeck" <wvandenbroeck@.discussions.microsoft.com> wrote in message
> news:2A416FD1-02F9-427D-9748-7F2CE4E08CDD@.microsoft.com...
>

accessing from T-SQL a database on another DBMS

Sunday, March 11, 2012

Access to SQLServer Database from Visual C# app and ASP.NET app

I've created a visual C# GUI that logs data over a LAN. The GUI is for Admin purposes only so it logs data and stores it (plus some admin stuff). The data that is logged is supposed to be served up to anyone on the network as a web app. I've got an instance of SQLServer running and the GUI works great but I can't seem to get the web app to connect to the database to read and display the data for general users?

I'm new to ASP.NET and SQL Server so a little extra explanation or steps that I'm missing wouldn't hurt me if you have the time to spare.

I get an error that says "Cannot get web application service" in Visual Studio when I try and drag and drop a SQLDataSource onto my pages and configure it. It then asks me to Choose My DataConnection from the drop down and there's no connection there...

I tried setting it up in the code behind page like I would in Visual C# (small code example below) by adding in using System.Data.SqlClient; etc.. and it throws me a nasty notice (see below). Obviously it doesn't like this user but how do I fix this so I can server up my DB to work for both my GUI and ASP apps? I noticed when looking at my DB that the owner is TUDOR\Windows is there a way to have multiple owners to a DB if this is what's wrong?

String conn = @."Server=TUDOR\sqlexpress;" +

"Integrated Security=True;" +

"Database=ATSDB";

// Specify SQL Server-specific connection string

SqlConnection dbconn = new SqlConnection(conn);

// Create DataAdapter object

SqlDataAdapter dbadpt = new SqlDataAdapter("SELECT FirstName FROM tblPersonInfo WHERE FirstName = 'Kim'", dbconn);

// Create CommandBuilder object to build SQL commands

SqlCommandBuilder dbcmd = new SqlCommandBuilder(dbadpt);

// Create DataSet to contain related data tables, rows, and columns

DataSet dbset = new DataSet();

// Fill DataSet using query defined previously for DataAdapter

dbadpt.Fill(dbset, "tblPersonInfo");

Server Error in '/ATS' Application.

Cannot open database "ATSDB" requested by the login. The login failed.
Login failed for user 'TUDOR\ASPNET'.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot open database "ATSDB" requested by the login. The login failed.
Login failed for user 'TUDOR\ASPNET'.
Source Error:

Line 32: Line 33: // Fill DataSet using query defined previously for DataAdapter Line 34: dbadpt.Fill(dbset, "tblPersonInfo"); Line 35: } Line 36: }

Source File: c:\Inetpub\wwwroot\ATS\text\Default2.aspx.cs Line: 34 Hi there,

ASP.NET web applications, by default, will run under their own account. In your case, this account seems to be TUDOR\ASPNET.

The problem seems to be that this account is not set up as a login in SQL Server. You need to create a login for TUDOR\ASPNET for your SQL Server and give it access to the required databases.

You can do this by (sorry if you already know this bit):

1) Open Management Studio Express (I presume from reading your code that you are using SQL Server Express Edition) and connect to your SQL Server Instance

2) Expand the node for your SQL Server instance and you will see a node labelled "Security"

3) Right click on the "Security" node and select "New > Login" from the context menu that appears

4) A new dialog window will appear. Enter "TUDOR\ASPNET" as the user and make sure that the Windows Authentication radio button is selected. Set the default database as required (in your case this will probably be ATSDB)

5) In the dialog, on the left hand side there should be an item called "Server Roles". Click on this and then define whatever server roles (e.g. sysadmin) TUDOR\ASPNET will have....What roles the user'll have is entirely up to you

6) In the dialog, on the left hand side there should be an item called "User Mapping". Click on this and then select which databases TUDOR\ASPNET will have access to (in your case, most definately select ATSDB). Also, select what database role (e.g. dbowner) the account will have....Once again, this is up to you to determine

7) Nothing else is particularly interesting so just click OK to finish the process

After you have given access to TUDOR\ASPNET to your SQL Server, you should be able to use your ASP.NET application without getting that error.

Hope that helps a bit, but sorry if it doesn't
|||That sounds like exactly what I need to do. I am using Sql Server Express, from what I've read that is what comes with Visual Studio 2005 but I can't find Management Studio Express? Is that something that only comes with Express Edition software? When I was asked to do this I went out and bought some O'Reilly books but I also bought a couple Express Edition books so I could get up and going fast so if the code looks unelegant that might be why or maybe it was the /SQLExpress that gave it away. Thanks for all the notes I didn't know how to do all that and if I can find Management Studio Express that would definetly solve all my problems. Is it under something else in Visual Studio 2005 Pro Edition?|||Hi there,

Management Studio Express is a seperate download. You can get it from the below link.

SQL Server Express Edition Downloads

After you download & install it, you should be good to go. When Management Studio asks you to connect, your server name, by reading your code should be TUDOR\SQLEXPRESS.

Using Management Studio Express you can connect via SQL Server Authentication (using an account like "sa") or Windows Authentication. If you connect via Windows Authentication but find you can't modify users then your account probably does not have the required privileges. If you switch to the "sa" account you should be fine.

Hope that helps a bit.
|||Thanks so much for your help and your explanations. That's a lot of typing to do to help out someone you don't know. Much appreciated. Works great.

Saturday, February 25, 2012

Access SQL Server Behind Firewall

I have an SQL server that resides on my local LAN. My local LAN is
behind a firewall that we do not maintain. We have been told that we
cannot have any additional ports opened on the firewall, meaning that
the ports used by SQL server are and will remain closed.
My dilemma is that I need to provide people outside my company view
only access, via our internet site, to data that is on my local SQL
server. My web server resides on a public network that is outside my
firewall. Is there a way that this can be done without opening the
ports on the firewall?Web Services
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
<harel.davidm@.gmail.com> wrote in message
news:1151082261.350657.124850@.m73g2000cwd.googlegroups.com...
>I have an SQL server that resides on my local LAN. My local LAN is
> behind a firewall that we do not maintain. We have been told that we
> cannot have any additional ports opened on the firewall, meaning that
> the ports used by SQL server are and will remain closed.
> My dilemma is that I need to provide people outside my company view
> only access, via our internet site, to data that is on my local SQL
> server. My web server resides on a public network that is outside my
> firewall. Is there a way that this can be done without opening the
> ports on the firewall?
>|||Arnie Rowland wrote:
> Web Services
>
Can you be a little more specific? Your answer doesn't really help as
is.|||Web Services, by design (and definition) use port 80 (http). Almost all
firewalls have port 80 open. Web Services are normally 'inside' the firewall
running on a web server (IIS).
With Visual Studio, you can create one or more web services (applications
that run on an IIS server). The web service receives a http post from the
web application, and then the web service retrieves the data from SQL Server
and passes it back to the web application in the response.
Also, SQL 2005 has the capability to host stored procedures as web services.
However, this should only be done when one is in complete understanding of
the security issues.
This is a lengthy and complicated discussion, inappropriate for this forum.
I suggest that you investigate Web Services in Visual Studio. Here is a
place to start.
http://msdn.microsoft.com/webservices/
Good luck.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
<harel.davidm@.gmail.com> wrote in message
news:1151082723.343995.161530@.g10g2000cwb.googlegroups.com...
> Arnie Rowland wrote:
>> Web Services
> Can you be a little more specific? Your answer doesn't really help as
> is.
>|||It is my understanding that to do this we will need to upgrade to SQL
2005 and Visual Studio 2005. Our application is running on SQL 2000
and was written in Visual Studio 6 and is not easily rewirtable.
Arnie Rowland wrote:
> Web Services, by design (and definition) use port 80 (http). Almost all
> firewalls have port 80 open. Web Services are normally 'inside' the firewall
> running on a web server (IIS).
> With Visual Studio, you can create one or more web services (applications
> that run on an IIS server). The web service receives a http post from the
> web application, and then the web service retrieves the data from SQL Server
> and passes it back to the web application in the response.
> Also, SQL 2005 has the capability to host stored procedures as web services.
> However, this should only be done when one is in complete understanding of
> the security issues.
> This is a lengthy and complicated discussion, inappropriate for this forum.
> I suggest that you investigate Web Services in Visual Studio. Here is a
> place to start.
> http://msdn.microsoft.com/webservices/
> Good luck.|||.NET Web Services can access data in any ADO.NET datastore -including SQL
Server 2000, Oracle, Exchange, Access, Excel files, etc..
You can create .NET Web Services using Visual Notepad -however Visual Studio
makes it easier.
SQL Server 2005 is not necessary for Web Services.
IIS and the .NET framework on the IIS server (and the development boxes) is
required.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
<harel.davidm@.gmail.com> wrote in message
news:1151088102.639755.88150@.c74g2000cwc.googlegroups.com...
> It is my understanding that to do this we will need to upgrade to SQL
> 2005 and Visual Studio 2005. Our application is running on SQL 2000
> and was written in Visual Studio 6 and is not easily rewirtable.
> Arnie Rowland wrote:
>> Web Services, by design (and definition) use port 80 (http). Almost all
>> firewalls have port 80 open. Web Services are normally 'inside' the
>> firewall
>> running on a web server (IIS).
>> With Visual Studio, you can create one or more web services (applications
>> that run on an IIS server). The web service receives a http post from the
>> web application, and then the web service retrieves the data from SQL
>> Server
>> and passes it back to the web application in the response.
>> Also, SQL 2005 has the capability to host stored procedures as web
>> services.
>> However, this should only be done when one is in complete understanding
>> of
>> the security issues.
>> This is a lengthy and complicated discussion, inappropriate for this
>> forum.
>> I suggest that you investigate Web Services in Visual Studio. Here is a
>> place to start.
>> http://msdn.microsoft.com/webservices/
>> Good luck.
>|||And this will work if my outside IIS server is unable to access my LAN?
I am told that we can go from inside out but not outside in.|||harel.davidm@.gmail.com wrote:
> And this will work if my outside IIS server is unable to access my LAN?
> I am told that we can go from inside out but not outside in.
>
You really need to read more about web services and understand them,
you're missing the point. The web service would run on an IIS instance
INSIDE YOUR FIREWALL. The IIS instance that sits outside the firewall
would talk to the web services running on the IIS instance inside the
firewall, using it as a sort of proxy to get data from your SQL Server
machine. The web services will NOT run on the IIS instance outside the
firewall, therefore it doesn't matter if that machine can see the SQL
Server machine.|||Tracy McKibben wrote:
> harel.davidm@.gmail.com wrote:
> > And this will work if my outside IIS server is unable to access my LAN?
> > I am told that we can go from inside out but not outside in.
> >
> You really need to read more about web services and understand them,
> you're missing the point. The web service would run on an IIS instance
> INSIDE YOUR FIREWALL. The IIS instance that sits outside the firewall
> would talk to the web services running on the IIS instance inside the
> firewall, using it as a sort of proxy to get data from your SQL Server
> machine. The web services will NOT run on the IIS instance outside the
> firewall, therefore it doesn't matter if that machine can see the SQL
> Server machine.
Maybe I am missing the point, but isn't that what these forums are for?
I asked a simple question, at least to me. I am not a
developer/programmer so allot of this is new to me. I am more of the
technical/hardware person here. I was just relaying the information I
was given by those who maintain the firewall. Regardless of how Web
Services works, the information I detailed is what I was told. I am
simply looking for a preferred means of providing information on my
Internet site..|||Bottom line.
With your current situation, you are not going to be able to provide any
data from SQL Server to the web server outside the firewall without opening
ports in the firewall for the web server to use to connect with the SQL
Server.
If you cannot open any ports, Web Services is an option. However, web
services will require significant programming expertise and can, if not
implimented correctly, have serious data security issues. It sounds like
that option is also not available for you.
If your web host provides any database support, you could 'manually' upload
data extracted from your SQL Server. If not, then you could manually upload
datafiles as csv, Access, etc. However, that presents severe maintenance and
latency issues. And then there is not a way to capture user interaction from
the web site and send it back to your database server. But it may be the
only option for you to explore.
You asked a 'simple' question. The question, and its followups, received
clear and informative -though complex responses. You have been directed to a
source of additional information.
I don't know how else we can help you.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
<harel.davidm@.gmail.com> wrote in message
news:1151093318.784026.187460@.y41g2000cwy.googlegroups.com...
> Tracy McKibben wrote:
>> harel.davidm@.gmail.com wrote:
>> > And this will work if my outside IIS server is unable to access my LAN?
>> > I am told that we can go from inside out but not outside in.
>> >
>> You really need to read more about web services and understand them,
>> you're missing the point. The web service would run on an IIS instance
>> INSIDE YOUR FIREWALL. The IIS instance that sits outside the firewall
>> would talk to the web services running on the IIS instance inside the
>> firewall, using it as a sort of proxy to get data from your SQL Server
>> machine. The web services will NOT run on the IIS instance outside the
>> firewall, therefore it doesn't matter if that machine can see the SQL
>> Server machine.
> Maybe I am missing the point, but isn't that what these forums are for?
> I asked a simple question, at least to me. I am not a
> developer/programmer so allot of this is new to me. I am more of the
> technical/hardware person here. I was just relaying the information I
> was given by those who maintain the firewall. Regardless of how Web
> Services works, the information I detailed is what I was told. I am
> simply looking for a preferred means of providing information on my
> Internet site..
>|||harel.davidm@.gmail.com wrote:
> Maybe I am missing the point, but isn't that what these forums are for?
> I asked a simple question, at least to me. I am not a
> developer/programmer so allot of this is new to me. I am more of the
> technical/hardware person here. I was just relaying the information I
> was given by those who maintain the firewall. Regardless of how Web
> Services works, the information I detailed is what I was told. I am
> simply looking for a preferred means of providing information on my
> Internet site..
>
The preferred means is to have your database server and web server
within close proximity, either living together within a DMZ, or the web
server in DMZ, data behind the internal firewall with a port opened.
What you're attempting to do is connect a web server that lives outside
of your environment to a database server inside your environment, but
you don't want to open a hole for it to talk through. It should be a
trivial thing for your network guy to open a port to the SQL machine,
and configure a rule to allow ONLY the IP of the web server to connect
to it. Put SQL on a non-default port, open that port up, and secure it
with a rule, you should be just fine.
I wasn't attacking you. Your question is not a simple one. You were
given a recommendation of using web services, I was merely suggesting
that you research them a bit further before discounting them or asking
further questions.
Good luck...

Tuesday, March 27, 2012

accessing from T-SQL a database on another DBMS

accessing from T-SQL a database on another DBMS

accessing from T-SQL a database on another DBMS

Sunday, March 11, 2012

Access to SQLServer Database from Visual C# app and ASP.NET app

Server Error in '/ATS' Application.

Cannot open database "ATSDB" requested by the login. The login failed.
Login failed for user 'TUDOR\ASPNET'.

Saturday, February 25, 2012

Access SQL Server Behind Firewall

SQL Alert

Blog Archive

About Me

Tuesday, March 27, 2012

accessing from T-SQL a database on another DBMS

accessing from T-SQL a database on another DBMS

accessing from T-SQL a database on another DBMS

Sunday, March 11, 2012

Access to SQLServer Database from Visual C# app and ASP.NET app

Server Error in '/ATS' Application.

Cannot open database "ATSDB" requested by the login. The login failed.Login failed for user 'TUDOR\ASPNET'.

Saturday, February 25, 2012

Access SQL Server Behind Firewall

SQL Alert

Blog Archive

About Me

Cannot open database "ATSDB" requested by the login. The login failed.
Login failed for user 'TUDOR\ASPNET'.