March 17, 2010 at 12:58 pm
Ahhhh, a history quiz...
We just discovered an old copy of Microsoft SQL Server Desktop Engine 2000 (version 8.00.760) on our network. Apparently, a Windows Admin installed it a long while back with piece of application software and did not realize that MSDE was part of the bundle. Consequently, we do not have any database backups.
In the short term, I need to find a way to connect to this database, configure database backups, and eventually import the data into a new database on our normal SQL 2005 database farm so we can deinstall MSDE on the problem server.
At this point, I can connect to the MSDE database when logged onto the server console using this syntax:
osql -S server_name\HPDSDB -E
Unfortunately, I cannot connect remotely to the database (i.e., the same syntax fails when attempting to connect from my local PC, which has the SQL 2005 Management Studio installed).
Other than osql.exe, no additional tools exist on the problem server. Consequently, I don't see a place to configure the network protocol so that I can connect from my local PC. I checked the Registry location where TCP should be:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\TCP
And that Registry key does not exist. The only thing I see is:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\Tcp
And the value of the key is set to 1433.
Any ideas? Does anyone remember anything about MSDE 2000?
March 18, 2010 at 7:10 am
Trying to get a response...
All help is appreciated!
March 18, 2010 at 9:19 am
Since you can't connect remotely, is there a firewall rule in place keeping you from getting port 1433?
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
March 18, 2010 at 9:44 am
Thanks for your response!
Prior to posting, I submitted a ticket to our Network folks to make sure that the port is open between my workstation and the server. They say the port is open. However, when I use a portping utility (something we downloaded from the Internet a while back), the connection fails. That could be because the Network folks didn't really open the port, or nothing on the remote server is using port 1433. I'm not sure how to verify the connectivity issue.
March 18, 2010 at 9:55 am
Open a Windows DOS prompt and try and telnet to your SQL Server on port 1433:
telnet <IP address> 1433
(Replace <IP address> with the actual IP address of the remote SQL Server)
If you're successful, meaning that the port is open, the screen will clear and you'll see the prompt just sitting in the top left corner blinking. If it's not successful, it'll say something like this after timing out:
U:\>telnet someserver 1433
Connecting To someserver...Could not open connection to the host, on port 1433: Connect failed
This is just to double check that your portping utility was correct. If so, get those network guys back on the phone and provide them with this info.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
March 18, 2010 at 10:28 am
We work at a secure site, and telnet has been removed. That is why I was using the portping utility I mentioned.
March 18, 2010 at 12:46 pm
I'm curious about this problem now, also, as I have to administer a few SS2K boxes and could see this happening to me. I still think it's a port blocking problem, you could download 'putty' and try and telnet using it. But, I think you might have more success posting this in the 2K forums, maybe some of the old school gurus could tell us how to determine the port number that the MSDE engine is listening on.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
March 19, 2010 at 2:20 am
Just looking at possibly solving this from another angle: is the application that is using MSDE one that is capable of using a full-blown SQL server? You might be able to transfer the database to a proper server and therefore not need to worry about backing up the antique!
March 19, 2010 at 8:56 am
Short Teram:
(Assuming you can logon to the server). Stop the SQL Server service and take a cold backup of the .mdf and .ldf files. Restart the service.
Download a copy of sp_backupdatabase from here - http://www.mssqltips.com/tip.asp?tip=1174
The instructions on the site are pretty easy to follow.
You can schedule a task in your windows scheduler to run this.
Sample:
RUN: sqlcmd -S server\Instance -E -i "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Backup.sql" -o "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Backup.output"
START IN: "C:\Program Files\Microsoft SQL Server\90\Tools\Binn"
Where backup.sql looks like this:
sp_BackupDatabase 'master', 'F'
GO
sp_BackupDatabase 'model', 'F'
GO
sp_BackupDatabase 'msdb', 'F'
GO
sp_BackupDatabase 'User_DB', 'F'
GO
Long Term:
You will have to coordinate with the owner of the application. I have asked where/what the connecion string is for the App and get a blank stare.
You can stop the SQL Server service, copy the .mdf .ldf files to another SQL 2005 server and attach.
The App owner will then need to change his connection in his App.
Hope that helps.
Tim White
March 19, 2010 at 3:23 pm
2 Tim 3:16 (3/19/2010)
Short Teram:(Assuming you can logon to the server). Stop the SQL Server service and take a cold backup of the .mdf and .ldf files. Restart the service.
Download a copy of sp_backupdatabase from here - http://www.mssqltips.com/tip.asp?tip=1174
The instructions on the site are pretty easy to follow.
You can schedule a task in your windows scheduler to run this.
Sample:
RUN: sqlcmd -S server\Instance -E -i "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Backup.sql" -o "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Backup.output"
START IN: "C:\Program Files\Microsoft SQL Server\90\Tools\Binn"
Where backup.sql looks like this:
sp_BackupDatabase 'master', 'F'
GO
sp_BackupDatabase 'model', 'F'
GO
sp_BackupDatabase 'msdb', 'F'
GO
sp_BackupDatabase 'User_DB', 'F'
GO
Long Term:
You will have to coordinate with the owner of the application. I have asked where/what the connecion string is for the App and get a blank stare.
You can stop the SQL Server service, copy the .mdf .ldf files to another SQL 2005 server and attach.
The App owner will then need to change his connection in his App.
Hope that helps.
Actually, we have inherited 5 of these rogue (for lack of a better term) SQL Server installations. I was finally able to access two of them through our internal firewalls, and I was able configure a script based SQL Server backup for both of them. The remaining 3 servers are the issue. I am essentially having to "break into" them to get them configured. (No, there is no documentation. That would take the challenge out of it. LOL) Unfortunately, all 5 of these servers have been a hodge podge of seemingly random SQL Server installations--various version (Enterprise Edition, SQL Express 2005, and MSDE 2000) and operating on an array of IP ports for SQL Server. So, I cannot rely on the default 1433 port to be the correct port, nor can I rely on our site standard non-default port to be active. (Yes, this has been a "fun" task.)
I am still looking for a way to determine the MSDE port that may or may not be active. At this point, I am grasping for straws...
March 22, 2010 at 5:03 am
Try looking in the SQL Error Log for the startup messages. This could give you the information you need.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 23, 2010 at 1:31 pm
This might help. There is a powershell script in the article.
http://www.databasejournal.com/features/mssql/article.php/3764516/Discover-SQL-Server-TCP-Port.htm
March 24, 2010 at 11:16 am
There are probably at least two reasons that you can't access it remotely. First off MSDE and SQL Express don't allow remote connections by default. Secondly TCP/IP is not enabled by default.
The easiest way is probably to install the SQL Server Client Tools (either 2005 or 2000) on the computer where the MSDE is running. This will allow you to configure it with the SQL Server Configuration tools and view it with the SQL Server Management Studio. The SQL Server Configuration tools give you the port if TCP/IP is enabled.
March 26, 2010 at 7:28 am
shew,
If you haven't been able to make any progress on reigning in your newly acquired MSDE instances, I may have found some information to aid you in bringing all of them into your control.
In a google search, I came across what seems to be an excellent article on configuring an MSDE instance:
Hands on how to configure the Microsoft MSDE
http://www.codeproject.com/KB/database/ConfigureMSDE.aspx
... since you can login to the local machines that run the MSDE instances, I hope that you will be able to use the article to get remote connections working, and ultimately some backups made!
Subtopics that caught my eye ...
Do you want to change the system administrator (sa user) password?
Login to the MSDE using the osql tool:
Using Win authentication:
C:\> osql ?E ?S localhost\myinstance
1> use master
2> go
1> exec sp_password @old = null, @new = ?newpassword?, @loginame = 'sa'
2> go
Password changed.
1>quit
Using SQL authentication:
C:\> osql ?U sa ?P password ?S localhost\myinstance
1> use master
2> go
1> exec sp_password @old = null, @new = ?newpassword?, @loginame = 'sa'
2> go
Password changed.
1>quit
Configure the server:
Run the SQL Server network utility. Open a command prompt and type:
C:\> svrnetcn.exe
Enable the "Named Pipes" protocol and the "TCP/IP" protocol. Click on "Properties" on the "TCP/IP" protocol. Here, you will configure the default port MSDE will be listening on if you want network access. Default is 1433.
Keep us posted on your progress.
Regards,
Key
March 27, 2010 at 8:33 am
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply