How can I convert download SQL Server 2005 to a licensed version.

I downloaded a 180 day trial version of SQL Server 2005, and have it running. I have purchased a 5 user workgroup version. I would like to apply the 5 user license to the version that is currently installed.

My preference is to not reinstall everything since I have everything configured as I would like, and it is working great. What is the best approach?

Paul

The only way to do this is to Upgrade your Evaluation Edition to the Workgroup SKU. To do this you should run the Workgroup installation program and select the edition of SQL Server that you already have installed instead of installing a new edition.

Michelle

|||

Will this leave the installed database and all of its configuration as is?

Or will it change any of the configuration?

The reason I ask, is that due to a lack of proper planning we did not have SQL Server installed prior to a vendor arriving to install their application. I quickly downloaded the trial version, and installed it and ordered the Workgroup Edition. The applicationn is in and running, and I now want to make everything legal, and to have it run past the 180 day evaluation period.

If upgrading will change the application, I will need to pay the vendor to reconfigure things, and cause me problems.

Paul

sql

How (un)secure is my remote SQL Server?

I've been provided with a server at a hosting company. The server is running W2K3 SP2 in its own workgroup (i.e., non-AD) configuration, but is not behind any type of hardware firewall; there is no VPN in place, either. I connect to the server via RDP using an extremely long and complex password. I'm using the newest version of the RDP client. The article "Hacking RDP" and the ensuing reader comments (http://mcpmag.com/columns/article.asp?EditorialsID=1699) indicate that using RDP in this fashion is relatively safe.

I installed SQL Server 2005 SP2 on this server. I set server authentication to 'SQL Server and Windows Authentication mode'. I created one obscure SQL Server login, using another extremely long and complex password. I also disabled the login for the 'sa' account.

Since installing SQL Server on this server, I've noticed thousands of Failure Audit events in the server's Application log:

Source: MSSQLSERVER

Description: Login failed for user X

where X equals 'administrator', 'root', 'server', 'database' 'sql', 'sa', etc.

These failure events occur almost non-stop, about a dozen per second, and come from a small pool of unknown IP addresses. The IP address seems to change every few hours. I'm guessing that someone is hoping that one of these names is an actual SQL Server login and is trying a brute-force attack to try to stumble upon a matching password. None of these logins are valid, but it's still disconcerting. Is this anything to be concerned about? I could have the hosting company block the IP addresses, but that seems like a losing battle.

Lastly, I used the Surface Area Configuration tool to allow local and remote connections, using TCP/IP only--so that I could begin interacting with this SQL Server from my PC, using both SQL Server Management Studio and my own Visual Studio code. For each method, I'm using the obscure SQL Server login that I created earlier--the one with the extremely long and complex password. How (un)secure is my traffic to/from this SQL Server? I don't believe that my credentials are encrypted, but I'm not sure how much of a risk this is nor do I know how else to more securely connect to SQL Server.

Given these circumstances, is there any way to make this resource more secure? Thanks!

Every hacker in the world (or so it seems) has automated tools that are always pinging port 1433.

You can increase your 'invisibility' by changing the IP port -preferrably to a high IP number, and make sure that port 1434 is closed on the windows firewall, and that the SQL Browser service is not running.

You are correct, that without a VPN, or SSL connection, you are sending the SQL password in clear text across the wire -in the context of your RDP connection.

While you have a small vulnerablity, it is relatively insignificant. Should you be concerned? If the database has sensitive data, protected data, or heads would roll if the data was hacked -get a VPN (or SSL) connection. Most hackers are seeking the 'low hanging fruit' of open and unprotected servers.

|||

Thanks. Please pardon my ignorance. How does blocking port 1434 on the Windows firewall help? Are there any negative side-effects of stopping the SQL Server Browser service?

>>You are correct, that without a VPN, or SSL connection, you are sending the SQL password in clear text across the wire -in the context of your RDP connection.

I think I was unclear in my original explanation. I'm using SQL Server Management Studio and Visual Studio-based applications from my PC to connect (via clear text) to the server's SQL Server over the Internet--not via the RDP connection. I mentioned RDP just in the context of how I administer the server, when necessary; RDP is not in use otherwise.

I'm not sure where to begin with a VPN; do you have any recommendations? The hosting company mentioned OpenVPN, but I'm not making much progress with that. I might make better progress via a SSL connection. Can you recommend any references on how to get started with that--in particular, regarding SQL Server traffic? Or any articles in general that apply to my kind of scenario? (unprotected server at a hosting company)

|||

If you are managing the SQL Server 'in the wild' (and not over the RDP) then you are really exposed and totally dependent upon the strength of your passwords and security model used in SQL Server.

Whatever solution you settle upon, it will have to be something that the hosting company is willing to deal with and support -since it has to be on their end and you probably cannot physically go there and set it up or mainain it.

If they suggested OpenVPN, I would start exploring that product. Personally, I have no experience with it.

Setting up SSL should be something that the host company should easily handle -but there is a cost for the certificate. (But not too onerous for the level of 'security' it provides.)

|||

Thanks again, Arnie. I forgot to mention that the SQL Server Browser service is already set to Disabled.

Your earlier comment got me thinking. I configured the Windows firewall to block all traffic other than port 3389 (RDP)--and that, with only the somewhat-static IP address of my PC. Of course, this had the immediate side-effect of preventing me from accessing SQL Server via SQL Server Management Studio on my PC. Next, I further configured the Windows firewall to allow port 1433 traffic--again, with just the IP address of my PC. SQL Server Management Studio was then able to connect to the server. That seems about as secure as I know how to make it for the time being. Now I just have to make sure that the IP address of my PC doesn't change!

I've opened another dialog with the hosting company. It's not clear to me how setting up a VPN will thereafter protect the server; is it that the server no longer responds to any other traffic other than that over the VPN port?

I don't mind purchasing an SSL certificate; just by itself, however, it's not clear to me how that will protect the server as a whole--like a VPN might. Wouldn't SSL result in just SQL Server traffic being encrypted?

|||

It may be time to bite the bullet and pay the extra for a static IP address (business expense and all...)

A VPN tunnel is somewhat similar to the RDP connection -with the extension that what is on the other end of the tunnel appears to be local to your network/computer. ALL internet traffic is encrypted between the endpoints. It is impossible for anyone to 'sniff' your packets and pick out data (or passwords).

For this usage, I think that VPN may be a better option that setting up SSL -unless your business needs SSL for its website.

|||

I agree that a VPN is the way to go. I've begun perusing the OpenVPN documentation, but it's a bit disjointed.

Can you recommend a different VPN?

I can't help but wonder whether Windows' RRAS might work; however, I have yet to find any helpful documentation about that feature.

Housekeeping blobs in a DB.

Hi,

VS2005

SQl workgroup 2005

I have a DB where I store alot of Blobs, the blobs are tagged up with alot of id. My concern is deleting them automatically on a regular basis, too keep the DB size down. I will be deleting them based on based on age(date/time) and what category the blob is classed as.

- My concern is how to delete blobs sort of equally dispersed among the categories so no category ends up empty although it may have old pictures in it.

Any tips, links or code is much welcome.

(my app will be acessing the DB though ADO .NET 3.0 c++/cli)

Cheers,

Andreas

WHat about using DELETE TOP(50 PERCENT) From SomeTable Where DateisOld ?

That would not delete every record which is old and will leave some of them still on the disk/Database.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
--|||

Jens,

Hi, that was a good suggestion, I wasn′t aware of the TOP command. It sort of is useful maybe if its used with

a > 1000 to ensure there is atleast 1000 blobs in each category?

now I need to solve such a query

Any help is appriciated!

Cheers,

/Andreas

|||A quick sample for your asked query would be:

DELETE
Production.ProductProductPhoto
FROM Production.ProductPhoto P
INNER JOIN
(
SELECT ProductPhotoId,ROW_NUMBER() OVER (PARTITION BY ProductPhotoId Order by ModifiedDate DESC) As Counter
From Production.ProductPhoto
) SubQuery
ON P.ProductPhotoId = SubQuery.ProductPhotoId
WHERE SubQuery.Counter > 1000

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Jens,

Thanks for your swift reply, will try what youre suggesting.

so far i′ve come up with a CTE solution that′s close but not totally there

WITH tempdata AS (SELECT ROW_NUMBER() OVER (PARTITION BY Category

ORDER BY Age DESC) AS rownum, ID FROM Imageblob)

DELETE FROM tempdata WHERE rownum < 1000;

hmm can you use DELETE TOP within a CTE? i didn′t get it to work.

Cheers,

/Andreas

|||

Whats your error message ?

THis works for me:

WITH tempdata AS (SELECT ROW_NUMBER() OVER (PARTITION BY ProductPhotoId Order by ModifiedDate DESC) As Counter, ProductPhotoId FROM Production.ProductPhoto)

DELETE FROM tempdata WHERE counter > 1000;

Remember that you want to do a >1000 instead of a <1000

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Jens,

Hi,

ah, the <1000 was a typo.

Error message? I don′t really get the hang of where to put the TOP cmd.

Cheers,

/Andreas.

|||YOu don′t need the TOP if you use the TVF.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Jens,

Ah,

I was toying with the idea to use TOP and Percentage rather than a fixed number to delete.

Cheers,

/Andreas

|||

Ok, is the problem solved then ? You can either delete a percentage or leave a threshold of rows in the table.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Jens,

I think so, ofc there are some more things I′ll try to add if I fail i′ll be back.

But thanks for your time and advice Jens!

Cheers

/Andreas

|||

Jens,

Ah, the phrase equal deletion is perhaps not accurate, I want to delete in the categories with most

rows. When i′ve created the CTE how can I from that deduce which Category in tempdata holds most

rows? Where do I fit a Count/Count_big?

And then delete from the category which has most rows and or delete more from the first, less from the second... etc

Code Snippet

count(age)

from Imageblob

Group by Category;

gives me rows per category, but with the CTE it gives me other number probably correct

based on the the tempdata is.

Cheers,

/Andreas

|||

The statement mentioned in your last post will elimenate the NULL values, therefore you probably have another count than the expected one of the CTE. For only deleting in the "biggest" category, you will have to query

Code Snippet

WITH tempdata AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY ProductPhotoId Order by ModifiedDate DESC) As Counter,
ProductPhotoId
FROM Production.ProductPhoto
)
DELETE tempdata
FROM tempdata F
INNER JOIN
(
SELECT TOP (1) COUNT(*),category FROM tempdata
GROUP BY category
ORDER BY COUNT(*)
) SubQueryForCount
ON SubQueryForCount.category = F.tempdata
WHERE counter > 1000;

Jens K. Suessmeyer

http://www.sqlserver2005.de