Port Blocking Preventing Deployment Of SSE Database
Jan 27, 2007
I am trying to deploy a SQL Server Express database to a remote SQL Server 2005 host. I was planning to copy and paste the data from my local tables to the remote tables using Management Studio Express but my ISP (NTL) appears to be blocking port 1433 and preventing me from getting a connection to the remote database.
Will an upgrade to SQL Server 2005 Developer Edition help me? I understand that it has a Backup & Restore Wizard but how does this work? Does it rely on a remote connection and will it fall foul of the NTL port blocking? Or, can the file that the Backup stage produces be copied into the web-based MSSQL Manager, that my web host provides, to restore to the remote database?
This morning I found that during the night someone hacked into my remote hosted SQL server 2005 database. Every record in a description field in each of three tables had everything after the first sentence replaced with the same URL. The three pages that used these tables badly infected anyone who viewed them in IE.I had my host restore the 12/30 backup, and I changed all the database and ftp passwords, now I want to be sure my code isn't giving anyone access. I'm using strongly typed datasets. This is my code behind code.Dim sCategory = Request.QueryString("cat")Dim ResourceAdapter As New ResourcesTableAdapters.ResourcesTableAdapterDim dF As Data.DataTable = ResourceAdapter.GetDataByCategory(sCategory)Dim numRows = dF.Rows.CountLabelCat.Text = sCategoryRepeater1.DataSource = dFRepeater1.DataBind() Is there anything I can do to better secure this? Oh, my connect string is in the web.config file only, but it isn't encrypted. I will be encrypting it today.Diane
Hi all.. I've been scouring the forums for about 6 hours to no avail. This is a really simple question. I'm trying to have a registration page that lets the user input name, email, desired username, and password. I want to check the username and email fields to make sure ppl cannot sign up twice. So from what I've gathered I have a couple of options:
1) i can set up a unique constraint on the database columns, 2) i can run a select statement before inserting, 3) i can store the whole database column in a variable then search through it.
My question is how to do option 2? All of my transactions are through a sqldatasource object in c#.
Hi I have a problem. Every time I select the first Item of a dropdownlist, it prevents the Insert to the database. There are 8 droplists on the page but the insert is only effected by the First Item of lstTheme selcetion. My Insert code is here: Any ideas?? Private Sub btnInsertChange_Click(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.CommandEventArgs) Handles btnInsertChange.Command
The database is configured for single publisher, many subscribers,merge replication. The maintenance plan started to fail a couple ofmonths ago and the database would not get backed up. After clearingall the locks, I am able to backup the database manually. The locksreturn again and I'm not able to backup the database with themaintenance plan. How can I get around the lock issue or solve it sothat I can backup the database again?Thanks,Chris
I have a user AD account SOPUSJSmith for example. I have given this account security admin at the SQL Server level and dbo at the database level. Is there any way to prevent them from using ODBC to pull data into an Access DB or an Excel spreadsheet?
we're in a business where customers often ask for a foolproof scheme that even prevents folks with DB privileges from fraudulently inserting, updating or deleting data. The scheme must be so air tight that a judge can be convinced of its reliability.
I am attempting to drop a database (sales), however I receive this message: Error 3274 is "Cannot drop the database 'sales' because it is published for replication." Yet, I no longer have any publications in this database. It seems that there is some orphaned information related to a publication that existed. Any help would be great.
We are running SQL Server 2014 Web Edition (64-bit). On three occasions now we have had two of our live secondary log shipping databases become unusable through the log shipping process. We ship logs to these databases every five minutes, and generally the process works fine. However, on three occasions in as many months we have suddenly received alerts warning us that the transaction log restores could not be performed on the secondary databases. The SQL Server Error Log revealed the following:
Date,Source,Severity,Message 10/13/2015 04:01:16,spid52,Unknown,Setting database option SINGLE_USER to ON for database 'ObfuscatedName'. 10/13/2015 04:01:17,spid52,Unknown,Starting up database 'ObfuscatedName'. 10/13/2015 04:01:17,spid52,Unknown,Recovery is writing a checkpoint in database 'ObfuscatedName' (8). This is an informational message only.
[code].....
An error occurred during recovery, preventing the database 'ObfuscatedName' (8:0) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support."
The extract above shows two previously successful restores to the secondary database. Suddenly, at 04:11:16, the database can no longer be started up or restored to. This is despite CHECKDB giving a clean bill of health only five minutes earlier. The result is that the last three lines are then repeated ad infinitum.
The only way we have managed to get around this issue so far is to replace the secondary database with a recent backup of the primary. Once this is in place, any outstanding transaction logs are successfully applied when the log shipping restore job next runs. However, this seems rather drastic.It is rather concerning that our warm-standby databases continue to become corrupted without any apparent explanation from SQL Server.
I've been put in charge of a SQL server recently and I'm completely new to maintaining SQL. For some reason several clients when they are doing their database processing I get network_io hangs and everything comes to a halt. I have to go in there and manually kill the SPID and everything continues where it left off.
SQL Setup
Server 2012 R2 SQL 2014 SP1 Dell 850 VM running in Hyper-V 50GB RAM FusionIO Card 10GB Ethernet between clients and server
All workstations are pretty much
Windows 7/8.1 10GB 64+GB of RAM Several CPUs SSD and SATA hard drives
It's very hit or miss when this occurs as well, however, it's always the same NETWORK_IO cause.
I've read on the Internet about how the client keep up with what SQL is sending. Could this be poor programming or do I need to investigate my network setup?
OS:Windows XP Professional Edition SQLServer: 2005(DE).SP2(Have installed 2 instances of 2005 in my local system) I am trying to do database mirroring,via SQL Server Management Studio. 1)configure security setup,(showed success). 2)when i start mirroring,i am getting the following error.
Alter failed for Database 'MIRROR_DEMO'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The server network address "TCP://tp190.DELL.servers.corp:5023" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=1418&LinkId=20476
------------------------------ BUTTONS:
OK ------------------------------
I have followed the microsoft link and i have enabled the port numbers aswell.
I am running MS SQL 2000 server. The table involved is only about10,000 records. But this is the behavior I am seeing.The local machine is querying the table looking for a particularrecord. Everything works fine.The remote amchine goes to clear the table and then insert all 10,000records, into the table the following happens.1) the local machines queries do not compilete until the remotemachine is done.2) the remote machine can take up to 6 minutes to do these 10,000insert operations. So nothing on the local machine works right forthese 6 minutes.I do not have access to the remote machines source to see what isrunning but I am told it is simply a for loop with a insert query init. Nothing of a locking natture.Any idea the types of things I should look for to track this down? Ifound this by doing SQL profiler profiling and finding the remoteoperations. Turn these operatiiosn off and the local machine worksfine again, with no other action.Thanks,David
I have a SQL server database that I am trying to connect to from VS2005 in the Server Explorer. I am not using the standard port for my SQL server listening port and I don't see where I can tell the Server Explorer to use a different port? Can someone point me in the right direction for connecting to my database? Thanks.
I've mapped a sqlserver machine within vs2005, but when I attempt to set the database connection, I dont see place to stick the port number (even in the advanced area)... it continues to fail when I test the connection.. I can telnet into the machine on the sql server port, so I know its open to me... Also - when I publish this to an IIS server, what is the syntax for placing the connection and port number in web.config? I've tried various settings, cant get it to work. <appSettings><add key="con" value="Server=12.123.123.123;PORT=1234;UID=*****;PWD=*****;database=mydb" /></appSettings>
How do you deploy changes to a database? Is there something you can run to compare the two versions and create a script to do this? I've heard about the DTS package (now DTSX I believe) but am not sure how to set one up. If anyone could point me to an article or something that might clear things up, it would be greatly appreciated. Thank you!
I am currently developing a big winforms project that connects to a SQL Server 2005 database.
The idea is that the application will be deployed on random clients whenever needed. These clients have neither SQL Server installed, nor is there a server to provide a central database. The only option is to include the database file with the deploy.
Now, I've searched all around the internet on how to do this. I just can't seem to connect to the database-file from my deployed version. Is there something special that needs to be done?
I have an application being developed in VS 2005 using SQL Express. I've built up some new roles and have several users pre-populated that I'd like to deploy to a shared hosting evironment. The problem is that I seem to keep getting: Database 'd:hostingmemberasangaApp_DataASPNETDB.mdf' already exists. Choose a different database name.Cannot attach the file 'd:hostingmember 2agilitysite1App_DataASPNETDB.MDF' as database 'ASPNETDB'. I saw another post with a similar issue, but that resolution has not seemed to help. Can anybody give me insights as to where the "asanga" is coming from? That might help me diagnose the issue. Thanks in advance
Greetings,I've been looking around all morning and can't seem to find a good answer on this so I thought I'd post a message here.I'm using the recommended practice of keeping the ASPNETDB.MDF data separate from my site data. (I have my site data located in separate databases but still keep them in the App_Data directory.)I'm using SQL Server 2005 Express on a Server 2003 box and I've implemented scheduled backups on the databases in the Sql Server.The questions I'm having are:1. Why isn't the ASPNETDB.MDF database getting attached to the server? Is it using User Instances?2. If it is using User Instance then isn't this degrading performance? If so, how to force it to not use User Instance?3. If User Instance is how it is supposed to work then how are we supposed to do a full backup of the ASPNETDB.MDF database if it isn't attached in the server? (xcopy doesn't seem to work on User Instances because of the dreaded "is being used by another process" error.)Any recommendations, advice, links, or answers to my questions would be greatly appreciated.
This question has been asked before, with vague responses I'm using Visual Web Developer 2005 Express, SQL Server 2005 Express and SQL Server Management Studio Express. When I create an application with Login controls, VWD automaticlly creates a ASPENTDB.MDF security database. I also create my own database for my application e.g. abc.mdf. In other words, I have two databases. However, my hosting company supports only one database. What do I (we) do? I have seen articles on: aspnet_regsql . Does this create a new database with all the security features of ASPNETDB.MDF built into my new database i.e. abc.mdf? (A) If yes, how do you run it with SQL Server Managment Studio Express? It sounds silly, but I need instructions here please ... Also, do you run this against abc.mdf or do you use it create abc.mdf? (B) If not, how do you achieve a single database scenario? I'm sure this is a very common deployment question, which is very confusing for most of us hobbyists that want to deploy their web applications
HiI'm using sqlexpress on development machine and on client server. I'm building a webapp using membership and role provided for asp. In web.config I have definet membership provider and role provider to point to my database.After web site deployment I get allways message : Failed to update database "c:appapp.MDF" because the database is read-only, and I'm forced to apply rights for ASPNET. More than that somethimes I must re-register users.I have tried to modify in web.config with <remove name="LocalSqlServer"/> <add name="LocalSqlServer"...> but I get errors if I try to design my dataset file:"Cannot get web application service".TIAPaul
I have developed a database in Visual Studio 2005 (c#) ,using SQL Express as my database, on my local machine and deployed it to a different machine where it is being used in a live environment. No problems.
However, I have continued developing the application on my local machine and have made numerous additions to the structure of the database (on the development machine).
My question...How do I go about deploying the new database structure to the live machine without losing the live data?
Hi, I work with a large team developing ASP.NET application that has a large database with over 50 complex stored procedures. It is proving more and more difficult and time consuming to centralise the development and update of the database changes and I was wondering if there were any best practises/tools that could be recommended. I have looked on the web for good articles and haven't found anything difinitive (except that Team Foundation Server is the way forward).. A brief background to the current process is that everyone develops on the same database, and then updates the stored procedure scripts in source safe (manually). Then when we do a new release someone builds a script of all the database updates and runs it. There are issues related to developers updating there stored procedures over other peoples and other concurrency. I am looking to move all the developers to start using local databases so that there work only effects them, but then this brings up problems of keeping all the local databases up to date whenever they get the latest source code. The only way I currently see is to build a database update program, that will run and update to the latest version. Surely this must be a common issue? Anyone have any good ideas/concepts? Also our setup is Visual Studio 2005, SQL Server 2005 and Source Safe 2005. Cheers, Andrew Thomas
I have searched hi and low for hours within the product and online - I cannot find how to have SQL 2005 produce a script to automatically repeat the creation of a database role I have tedious manually created.
I have tried "script object as, create to,<>" - single-liners are produced - no details. More single line results from "<Database>, All Tasks, Generate Scripts".
Am I missing something or is this a bug?
€¦also to note after review blogs and other sources the use of the SQL2005 methods related to security and new schema objects - I feel€¦ dreadfully wrong - what is claimed feels like hype, unsupported by examples and usage cases feels like we better just make all automated processes sysadmin; I know learning curves can be strong but I am not this green or that stupid - where are the DOCS/training materials?
I deployed my application to my newly installed xp pro laptop
The prerequisites are installed
Now that I want to testdrive my database application I get this error:
An attempt to attach an auto-named database file c:/program files........................MyDB.mdf failed. A database with the same name exists, or specified file can not be opened, or it is located on UNC share.
I want to create deployment for MS SQL 2005 Express database, that creates database, tables and inserts data. I have made a similar with VisualStudio 2003 and MSDN
I am trying to write a short application within which I would like to embed a SQL Server Express database, all of which would be deployed via ClickOnce through a web site. The installation process seems to be working without trouble, including installation of the .NET Framework 2.0 and SQL Server Express 2005.
When the application launches on the new machine, however, I get a big Microsoft .NET Framework "Unhandled exception" error dialog box that indicates the database already exists and that the database file could not be attached. I am working with a clean virtual machine that I know has never had the database installed on it.
I fear I am overlooking something quite straightforward, but since this is the first time I have ever attempted to build a data-bound application, I am not sure where I am going wrong.
Perhaps the source of the problem, or a related problem, might be revealed even when I run the application in debug mode from within Visual Studio. I can run the application once, but if I try to run it again, I get an error when I try to open the database connection that is similar in content (database already exists...could not attach). I can run the SSEUtil and detach the database and then run the application again in debug mode and it works no problem (the first time!).
Lastly, if I install the application on the machine on which it was developed and make sure the database is detached, it will run without any trouble (even repeatedly). But, on a new blank machine, there is no database listed that matches my database name, so I cannot try to detach anything!?
1 ALTER PROCEDURE links_GetAllLinks 2 @startRowIndex INT, 3 @maximumRows INT, 4 @sortExpression nvarchar(50) 5 AS 6 7 8 9 10 11 SET @startRowIndex = @startRowIndex + 1 12 --SELECT @TopicsFrom = CASE @TopicsDays WHEN '1' THEN DATEADD(day,-1,getdate()) WHEN '2' THEN DATEADD(day,-7,getdate()) WHEN '3' THEN DATEADD(day,-14,getdate()) WHEN '4' THEN DATEADD(month,-1,getdate()) WHEN '5' THEN DATEADD(month,-3,getdate()) WHEN '6' THEN DATEADD(month,-6,getdate()) WHEN '7' THEN DATEADD(year,-1,getdate()) ELSE DATEADD(year,-1,getdate()) END 13 -- populate the table CAST(getdate() as int) 14 15 16 IF @sortExpression LIKE '' 17 BEGIN 18 SET @sortExpression = 'links_Links.link_date' 19 END 20 DECLARE @sql nvarchar(4000) 21 SET @sql = 'Declare @Links TABLE 22 (RowNumber INT, 23 link_id INT, 24 cat_id INT, 25 cat_title VARCHAR(100), 26 sub_id INT, 27 sub_name VARCHAR(100), 28 link_aproved BIT, 29 link_name VARCHAR(100), 30 link_url varchar(100), 31 link_desc varchar(500), 32 link_date datetime, 33 link_rating INT, 34 link_ratingscount INT, 35 link_ratingvalue INT, 36 link_poster uniqueidentifier, 37 link_comments INT, 38 hit_date datetime);INSERT INTO @Links 39 SELECT ROW_NUMBER() OVER (ORDER BY ' + @sortExpression + '), links_Links.link_id, links_Links.cat_id, links_Categories.cat_title, links_Links.sub_id, links_SubCategories.sub_name, links_Links.link_aproved, links_Links.link_name, links_Links.link_url, links_Links.link_desc, links_Links.link_date, links_Links.link_rating, links_Links.link_ratingscount, (links_Links.link_rating/links_Links.link_ratingscount) as link_ratingvalue, links_Links.link_poster, links_Links.link_comments, links_Links.hit_date 40 FROM links_Links INNER JOIN 41 links_Categories ON links_Categories.cat_id = links_Links.cat_id INNER JOIN 42 links_SubCategories ON links_SubCategories.sub_id = links_Links.sub_id 43 WHERE links_Links.link_aproved = 1;SELECT * from @Links 44 WHERE RowNumber BETWEEN ' + CONVERT(nvarchar(10), @startRowIndex) + ' AND (' + CONVERT(nvarchar(10), @startRowIndex) + ' + ' + CONVERT(nvarchar(10), @maximumRows) + ') - 1' 45 46 47 48 EXEC sp_executesql @sql 49 50 51 RETURN at row 39, i define a field by dividing to values. I use these for ratings (finding the mean). The problem is, when a new link is submited, it has no ratings, so it performs a divide by zero. how do I check if the column "link_ratingscount" is zero? if it is, then return a zero, and if it is not zero, then do the divide?
I am building my first ASP.Net app from scratch and while working on the DAL I came across the problem of SQL Injection. I searched on the web and read different articles but I am still unsure about the answer. My question is should I add
Add in Parameters to my C# code to avoid SQL Injection. What is the best practice. I am unclear if the stored procedure already helps me avoid SQl Injection or if I need the add in parameters in the C# methods to make it work. I need some help. Thanks, Newbie
My C# update method in the DAL (still working on the code)
private static bool Update(AvatarImageInfo avatarImage) { //Invoke a SQL command and return true if the update was successful. db.ExecuteNonQuery("syl_AvatarImageUpdate", avatarImage.AvatarImageID, avatarImage.DateAdded, avatarImage.ImageName, avatarImage.ImagePath, avatarImage.IsApproved);
return true; }
I am using stored procedures to access the data in the database.
My update stored proc
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO
ALTER PROCEDURE [dbo].[syl_AvatarImageUpdate] @AvatarImageIDint, @DateAddeddatetime, @ImageNamenvarchar(64), @ImagePathnvarchar(64), @IsApprovedbit AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; BEGIN TRY UPDATE[syl_AvatarImages] SET [DateAdded]= @DateAdded, [ImageName]= @ImageName, [ImagePath]= @ImagePath, [IsApproved]= @IsApproved WHERE[AvatarImageID] = @AvatarImageID RETURN END TRY BEGIN CATCH --Execute LogError SP EXECUTE [dbo].[syl_LogError]; --Being in a Catch Block indicates failure. --Force RETURN to -1 for consistency (other return values are generated, such as -6). RETURN -1 END CATCH END
