I am facing a strange issue with accessing data from SQL server 2000. We are using SQL Server authentication for connecting to SQL Server 2000 and ADO .Net SQL client in the application. The problem here is, we have databases installed in one of the servers in some other office location that is in different state but on same domain. We do development from two locations that is one from Virtual Machines hosted on servers where Database Servers are located and on our local desktops in India.
Now when I developed a test client in Virtual Machines which will call a web service to get datasetof some data and show them in DataGrid. When I run the test client in debug mode from Visual Studio 2003, then I am getting data in the DataGrid. Even when I just run the exe from Virtual Machine still I am getting data.
But when I copy debug folder of the test client to the local desktop in India and when I run the Test client I am not getting any data loaded on to the DataGrid. I am getting an empty dataset when I am running from the local desktop.
Also I have converted the VS2003 project to VS2005 and when I run the application from Local Desktop then I am getting the data loaded fine. I could not understand what is the problem, what is the difference between accessing data from VS2003 and VS2005 using webservice.
Is this a firewall issue or I am missing any thing here. The webservice method I am calling to get data is actually returning dataset when I am calling this method directly from the browser also and even through application in virtual machine.
I am still not able to come to terms with this problem. This is severely affecting our deployment. Please help us resolve this problem. Please let me know if you need more information.
I am new to internet development and would like some advice on the technology used to access a SQL database that sits on a network behind a firewall.
** ASP .NET Page ** -> ** Web Server ** -> ** FIREWALL ** -> ** SQL **
So to give an example; from an ASP .NET page on the internet, I would like to populate a DataGrid with the contents of a single table from a SQL database. The SQL database is sitting on our company network behind a firewall.
Could someone please explain / point me in the right direction in how the ASP .NET page / Web Server can securely access the SQL database.
I have a Windows Server 2003 with SQL Server 2005. I´ve configured the following itens to allow remote connecitions:
[code] WORKAROUND Warning If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.To work around this problem, follow these steps on the computer that Windows XP SP2 is installed on:
1. Make sure that the Log On As account for the MSDTC service is the Network Service account. To do this, follow these steps: a. Click Start, and then click Run. b. In the Run dialog box, type Services.msc, and then click OK. c. In the Services window, locate the Distributed Transaction Coordinator service under Name in the right pane. d. Under the Log On As column, see whether the Log On As account is Network Service or Local System.
If the Log On As account is Network Service, go to step 2. If the Log On As account is Local System, continue with these steps. e. Click Start, and then click Run. f. In the Run dialog box, type cmd, and then click OK. g. At the command prompt, type Net stop msdtc to stop the MSDTC service. h. At the command prompt, type Msdtc €“uninstall to remove MSDTC. i. At the command prompt, type regedit to open Registry Editor. j. In Registry Editor, locate, and then delete the following subkey: HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSDTC k. Close Registry Editor. l. At the command prompt, type Msdtc €“install to install MSDTC. m. At the command prompt, type Net start msdtc to start the MSDTC service.
Note The Log On As account for the MSDTC service is set to the Network Service account.
2. To allow the network transaction, you must enable MSDTC. To do this, follow these steps: a. Click Start, and then click Run. b. In the Run dialog box, type dcomcnfg.exe, and then click OK. c. In the Component Services window, expand Component Services, expand Computers, and then expand My Computer. d. Right-click My Computer, and then click Properties. e. In the My Computer Properties dialog box, click Security Configuration on the MSDTC tab. f. In the Security Configuration dialog box, click to select the Network DTC Access check box. g. To allow the distributed transaction to run on this computer from a remote computer, click to select the Allow Inbound check box. h. To allow the distributed transaction to run on a remote computer from this computer, click to select the Allow Outbound check box. i. Under the Transaction Manager Communication group, click to select the No Authentication Required option. j. In the Security Configuration dialog box, click OK. k. In the My Computer Properties dialog box, click OK.
3. Configure Windows Firewall to include the MSDTC program and to include port 135 as an exception. To do this, follow these steps:
a. Click Start, and then click Run. b. In the Run dialog box, type Firewall.cpl, and then click OK. c. In Control Panel, double-click Windows Firewall. d. In the Windows Firewall dialog box, click Add Program on the Exceptions tab. e. In the Add a Program dialog box, click Browse, and then locate the Msdtc.exe file. By default, the file is stored in the Installation drive:WindowsSystem32 folder. f. In the Add a Program dialog box, click OK. g. In the Windows Firewall dialog box, click to select the msdtc option in the Programs and Services list. h. Click Add Port on the Exceptions tab. i. In the Add a Port dialog box, type 135 in the Port number text box, and then click to select the TCP option. j. In the Add a Port dialog box, type a name for the exception in the Name text box, and then click OK. k. In the Windows Firewall dialog box, select the name that you used for the exception in step j in the Programs and Services list, and then click OK. [/code]
But, when the Windows firewal on the server is "On", remote connections are not allowed, despite I´ve configured the Exceptions on the firewall.
We have 1 machine running SQL Server 2005 x64 (64 bit). The other machine is backing this SQL Server up. It is a 32-bit machine, so it requires (?) a 32-bit version of SQL Server 2005.
Would this back-up machine work correctly on a database previously managed by a 64-bit machine and vice versa.
I have trying to access a 2nd SQL Server instances running on my WHS 2011 server from my Windows 8.1 Pro client.
The first SQL Server instance I installed is SQL Server 2014 Developer. This has been in place for many months and access from my client pc is not a problem. The Developer instance was installed using standard settings so uses TCP port 1433 and I have verified this by checking the firewall rules. This instance name is <SERVERNAME>
As I want to host a personal website on my server I installed SQL Server 2014 Express as a 2nd instance on my WHS 2011 box for website database purposes. After this installation both instances are accessible via Management Console on the WHS 2011 box.Â
The second instance is named as <SERVERNAME>SQLEXPRESS.
However, I am unable to access the SQLEXPRESS instance using Management Console on my client pc. I know it is a firewall issue as when I turn off the firewall on the WHS 2011 box I can connect successfully. The errors reported have resulted in my trying many things to resolve the problem but none have worked, e.g.
Use the default SQLEXPRESS instance to listen on all TCP ports (default installation option). I checked the necessary firewall rules were set up correctly and they were, i.e. UDP ANY and TCP ANY for the SQLEXPRESS sqlservr.exe file.
Change the default SQLEXPRESS instance to list on a specific TCP port, e.g. 19344
Create an ALIAS for the SQLEXPRESS installation
The instance is definitely set up for Remote Access and is not Hidden.
Are there any other firewall rules I need to apply?
HelloI have a project where I need to update a few tables in a SQL Server 2000database which resides behind a firewall, ie port 1433 is blocked. I alsoneed to verify the updates were successful.I am a C# developer. What are my options in doing this.. web services?John Dalberg
I'm trying to adminster a remote MS SQL server from inside my company's firewall. I have the correct permissions to pass traffic through the firewall, but I need to authenticate to it first. When I try to register this remote SQL server through Enterpise Manager, it of course does not let me because of the firewall. Is there any way I can pass the firewall ID and password during the server registration process, and then pass the SQL server ID and password? I'm no SQL server expert by any means, but it doesn't look like it's possible. Are there any third party utilities that may accomplish this?
Hello, I have a developer here who wants to be able to access two databases on different servers with the same query statement. Both servers have SQL Server 7.0 ??? Any suggesstions?
we have some Access databases that are password protected. I get an error when I try an access these through a Linked Server. If I remove the password there is no problem.
I have tried adding the password to the security Information with a user of admin but this fails with a 7303 error. Should I not use admin as the user ??
Hello,I have an sql server 2000 on the network and one installed locally inmy computer. I use access as front-end.I go through odbc to connect to the sql server on the network.I was wondering if it is possible to setup the access file to link toeither server.I need to do this because I would like to use the local server as testenvironment. I know I could achieve that by creating 2 users on my XPclient and having odbc liking depending of the users.Is any other way to do that without login and logoff everytime I wantto use a different database.thanks,Giovanni
I know the SQL syntax to join two tables in different databases on the ame server, but does anyone know if a SQL statement will allow you to join tables from different databases on different servers?
I have NT SQL 7 and Web Server on same box. I have a new server with W2K and SQL 2000 installed (no IIS). I am migrating the databases over and when done I will install W2K on the old box and it will remain the IIS Web server front end to the new SQL 2000 Server.
On the old configuration, IIS and SQL 7 on the same box, I added the IUSER to the database from the local computer users list when needed for web access. How do I add that user now that the boxes are seperate? The acccout is not a local account on the new machine and the account is a loca account on the other machine so it does not authenticate to the domain so I can't pull it in from there.
I'm using MSSQL7, NT authentication and application roles so only my application can access the data. Also, other applications (like Excel) can not access the data and read it. So far, so good...
Yet, I noticed that if I try to access the SQL Server from another SQL Server on the network, it is allowed to see the list of tables, SP, etc. It is not allowed to open the table, but the Import/Export wizard is working and will allow retrieving data from the secured tables. If I change to MSSQL authentication, any user will be able to access the data from my application and I don't want that either.
Unless I'm missing something, this is a big problem, especially today where any VPN connection with valid user name and password can actually log in to the domain and therefore connect to the database via SQL Server.
By the way, the server still must allow access to users via applications so logins must exist. I just don't want other SQL servers on the network to be able to connect to and import/export, view table and SP, etc.
I'm using MSSQL7, NT authentication and application roles so only my application can access the data. Also, other applications (like Excel) can not access the data and read it. So far, so good...
Yet, I noticed that if I try to access the SQL Server from another SQL Server on the network, it is allowed to see the list of tables, SP, etc. It is not allowed to open the table, but the Import/Export wizard is working and will allow retrieving data from the secured tables. If I change to MSSQL authentication, any user will be able to access the data from my application and I don't want that either.
Unless I'm missing something, this is a big problem, especially today where any VPN connection with valid user name and password can actually log in to the domain and therefore connect to the database via SQL Server.
By the way, the server still must allow access to users via applications so logins must exist. I just don't want other SQL servers on the network to be able to connect to and import/export, view table and SP, etc.
Having a little problem with linking to Access tables. I have an Access database that has in it, some linked tables. After setting up the linked server in SQL server, none of the linked tables in Access show up, only the tables that were created or imported show up.
Is there anything you can do to get the linked tables in Access to show up under the tables list in the SQL linked server?
Being an IT consultant, I have to visit a number of customers having applications running on SQL Servers that implement trusted connection security model. In addition, all of these companies have their own DOMAIN settings, and security implementation.
How can I access to databases without changing the DOMAIN settings of MY notebook? I do not want them to switch to SQL security model, and I do not want to create any security hole in those companies.
What shall I want from the system administrator so that I can login into their domain without changing my domain settings?
I'm looking to deploy some SQL Server reports and I want to restrict the access that the users have. Currently when connecting to the reports site they have access to a lot of functionality through the header bar, for example - Properties - New Folder - New Data Source - My Subscriptions - Site Settings - Search etc.
How can I disbale or hide all these options so that all the user sees is the list of reports?
We have been asked to look into using stored procedures with SQL Server 7.0 as a way to speed up a clients site. 99% of all the articles I have read along with all the books all say Stored Procedure should be used whenever possible as opposed to putting the SQL in your ASP script. However one of my colleagues has been speaking to Microsoft and they said that that they were surprised that our client wanted to use Stored Procedures as this was the old method of database access and that now he should really consider using COM objects for data access as itis much faster. Has anyone got any views on this or know of any good aticles regarding this matter ?
I have recently upgraded to SQL2014 on Win2012. The Access front end program works fine.
But, previously created Excel reports with built in MS Queries now fail with the above error for users with MS 2013. The queries still work for users still using MS 2007.Â
I also cannot create any new queries and get the same error message. If I log on as myself on the domain to another PC with 2007 installed it works fine, so I don't think it is anything to do with AD groups or permissions.
coycoy wrote:you wanted to join some columns coming from two different tables...right? if that so, use an SQL query. Try using the "inner join" statement. i wanted to combine the data from two tables. these two tables belong to different servers: table1 is from sql server found in cerebrum station and table2 is from mysql server found in copernicus station. i have this code but the problem is i can only use this code if the two tables belong in the same database server. </P> <P>string limitReghrsvalue = "select statuslog.ActId as STATUS_ID,DalsDataNew.ID as MANHOUR_ID,statuslog.ActDate as DATE,statuslog.PrjCode as PROJECT_CODE,statuslog.MapNumber as MAP_NUMBER,statuslog.Activity_Code as ACTIVITY_CODE,DalsDataNew.ActivityMedium as MEDIUM_CODE,statuslog.RegHrs AS REGHOURS,statuslog.OTHrs AS OTHOURS,statuslog.Status AS STATUS,DalsDataNew.Flag,DalsDataNew.Approvedby from statuslog,DalsDataNew where statuslog.PrjCode = DalsDataNew.ProjectCode and statuslog.PIN = DalsDataNew.PIN and statuslog.ActDate = DalsDataNew.Date and statuslog.Activity_Code = DalsDataNew.ActivityCode and statuslog.RegHrs = DalsDataNew.RegHours and statuslog.OTHrs = DalsDataNew.OTHours and statuslog.PIN = 'P120' and statuslog.ActDate >= '"+this.firstdate.Text+"' and statuslog.ActDate <= '"+this.lastdate.Text+"'";</P> <P> Sql Server in Cerebrum: database dals DalsData ID | Date | PIN | ProjectCode | ActivityCode | ActivityMedium | RegHrs | OTHrs | Approvedby | Flag 123 |9/17/2005| P120| 1234 | B | W(P) | 5.50 | 0.00 | P083 | 1 124 |9/17/2005| P120| 1234 | I | W(PC) | 1.50 | 2.25 | | MySqlServer in Copernicus: database stat Statuslog ActID | ActDate | PIN | ProjectCode | MapNumber | ActivityCode | RegHrs | OTHrs | Status(%) 1 | 2005-9-17 | P120 | 1234 | map01 | B | 5.50 | 0.00 | 100 2 | 2005-9-17 | P120 | 1234 | map01 | I | 1.50 | 2.25 | 75
the output in datagrid should be: ID | ActID | Date | ProjectCode | ActivityCode | MediumCode | MapNumber | RegHrs | OTHrs | Status | Approvedby | Flag 123| 1 |9/17/2005| 1234 | B | W(P) | map01 | 5.50 | 0.00 | 100 | P083 | 1 124| 2 |9/17/2005| 1234 | I | W(PC) | map01 | 1.50 | 2.25 | 75 | | could someone help me how would i do this?
I am trying to create a trigger on table A existing in Database A on Server A which on insert,update,delete would reflect these changes on table B in DatabaseB on Server B. I wrote the following syntax but it doesnt work
CREATE trigger trg_upd_tableA On dbo.tableA for UPDATE as --update the fields declare @cust_no varchar SELECT @cust_no = cust_no FROM inserted
update ServerB.DatabaseB.dbo.tableB set entity_type_cd = 'MFG', cust_no = 6699, name_en = 'NOVARTIS', status ='A', create_date=getdate() ,modify_date=getdate() where cust_no= @cust_no
I get the error below
Another user has modified the contents of this table or view ; the database row you are modifying no longer exists in the database
Database Error: '[Microsoft][ODBC SQL Server Driver][SQL Server] MSDTC on server 'DatabaseB' is unavailable'
I would like anyone to reply to me as soon as possible please because i need the solution urgently
I am attempting to move a User Database from the Production Server to a Training Server. What is the best/most simplistic way for me to accomplish this task and place this copying action on a schedule of say "every saturday morning @ 7am"
I tried the "Copy Database Wizard" within Enterprise Manager and it successfully copies the database, however when I try to schedule it to happen at a different time...it does not copy the database. It seems to only work when I tell it to perform this action now.
I need some help with a web application we are running. Is it possible to have 2 seperate instances of SQL 2005 running and each one pointing to the same DB. We want to load balance our users on the different DB servers. They are performing read and write transactions.
I've got two servers with sql 2005 express on, the first is a shared server at our isp, we rent one database from them. The other server is our own and we have full sa access.
Is there an easy way to copy the database from the shared server onto our own server. We're unable to use the backup.
Hi, I read the following article related to "Federated Database Servers" But i am not getting what is this "Federated Database Servers", May be the language of this article i think little bit difficult. So, can anyone please explain me this. Wht are thse servers and when, why and where we use these servers?????
I need to transfer my database from one server to database on another server every 24 hours. I can create windows application but it will be cumbersome to write bulk of code . So can u suggest me some service or any other way through query or stored procedure by job scheduling which can run every 24 hours and move my data from one database on one server to another sql database. Both databases are sql server 2000 but servers are diffeerent so how to connect them while transferring dbs. ? plz help me , its urgent.
