Peer-to-Peer Questions #26:

July 7, 2000

Editors Note This article, culled from the Microsoft TechNet Web site (https://www.microsoft.com/technet), answers the most interesting questions received on the peer -to-peer discussion groups over the past few weeks. To post your own questions, visit the TechNet discussion groups at https://www.microsoft.com/technet/community/newsgroups/default.mspx.

Topics this edition:

• SQL Puzzle

• PDC, BDC, WINS, RAS, DNS, DUN, LANs, and, oh yes - ISPs

• Restore SQL Backup – Renaming It in the Process

• Securing a Web Server

• Querying Index Server via Active Server Pages

On This Page

SQL Puzzle !!

SQL Puzzle !!

Q: We begin with a question posted by IT Pro Mansoor Azam - the following is a classic SQL query query:

I want to get the previous month's data. Obviously, it should have something like this:

where date between '5/1/2000' and '5/31/2000'

But the query should work for all months. It should simply get the data of the previous month without having to change the query every month. I have solved this, but I think it won't work if the month is January.

A: Fellow SQL guy Ervin Kaljola seems to know his stuff. Here is his suggestion to Mansoor:

declare @d1 datetime
declare @d2 datetime
select @d1 = cast(left(convert(varchar, dateadd(mm, -1, getdate()),
112), 6) + '01' as datetime)
select @d2 = dateadd(dd, -1, cast(left(convert(varchar, getdate(), 
112), 6) + '01' as datetime))
where date between @d1 and @d1

Editorial $0.02 worth:

Excellent SQL statement, Ervin! Now, in Ervin's query, specifically in the SELECT statements where the two variables are assigned (to 'd1' and 'd2'), you might notice the number "112" specified following the getdate function. That number is the date format style, of which there are several to choose from. More information on this can be obtained in SQL Server 7.0 Books Online ("BOL"): query on "cast and convert". (Include the quotes when entering your search.) A full explanation is in BOL, but I thought I'd provide the style table here.

* The default values (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the century (yyyy).

** Input when converting to datetime; Output when converting to character data

PDC, BDC, WINS, RAS, DNS, DUN, LANs, and, oh yes - ISPs

Q: Either IT Pro Paul Prince has a problem with simply too many acronyms, or a good serving of alphabet soup—I can't tell which. So, let's look at his dilemma:

We have two servers, A & B. Server A is PDC, WINS, and DNS. Server B is RAS and Exchange.

When Server B dials up our ISP to get mail, it cannot ping hostnames on our LAN or be pinged. As soon as the connection is terminated, DNS resumes ok.

Looking at IPCONFIG when dialed up and not dialed, it appears that when not dialed up, the DNS server shown is our local one. When dialed up, the DNS settings for the ISP override our local, hence no local DNS resolution.

What's going on, what do I do? Is it a routing problem?

A: IT Pro Clive Richardson steps in and offers the following suggestion:

If you're not using something like RRAS to handle the dialing to the ISP, what is probably happening is something quite normal and expected. Dialup connections (like from DUN) usually get a temporary IP address, and in networking terms, your Server B is on your ISP's LAN and not on yours for the duration of the dialup. It does this because you cannot be on two subnets at once...it's not allowed.... routing loops, etc. I don't know what you're using on Server B to handle the dialup, but it sounds like that is your area of focus to fix this problem.

Clive Richardson

To this, Paul responds with his own resolution report:

The fix is as follows:

Make server B a router, pointing the default gateway back to itself. Put a static route on Server B to capture all LAN/WAN traffic and shove local calls to the original router, anything else out onto the NET. Now, when dialed up to the ISP, I can ping any NET or local LAN/WAN address within the company.

Paul Prince

Restore SQL Backup – Renaming It in the Process

Q: I restored a database using a back-up utility other than the one provided by SQL server. I restored this database to its original location, however I renamed the database, to avoid overwriting of my current database. I only need this database to recover some data I lost not the entire database.

How do I get SQL Server to recognize this restored, renamed database?

Angelique Worrell

A: SQL Server MVP Tibor Karaszi quickly replies (like 15 minutes after Angelique's posting.) Here is Tibor's suggestion:

You probably want to check out the documentations for the sp_attach_db stored procedure.

And not 16 minutes after that, Angelique replied that she had attained resolution. Elapsed time from Angelique posting her "Serious problem" on the newsgroups to solution (including her thoughtful follow-up reply to the newsgroups): 31 minutes. Not bad. Anyway, here is Angelique's resolution follow-up:

I just found the answer. This code worked:

create database [database name]
on primary (filename='[filename]')
for attach
go

Securing a Web Server

Q. This question is from "da flava" and it concerns securing a Web server. Let's get right to it:

How do you disable the IPC$ share? Is it possible for a user to connect to this share from outside of

the network? It is my understanding that this is a security issue on Web servers. Am I correct? Thanks for the help. da flava

A: Yes, you are correct. This is a security issue on a Web server. What you need to do is unbind File And Printer Sharing and Client for MS Networks with all protocols that are also bound to your Network Adapter that is on the Internet. For example, a Web server that has 2 NICS (one for the inet connection, one for your internal network) might have:

• Inet Adapter:

  - TCP/IP

  - File And Printer Sharing- Client for MS Networks- Client for Netware Networks- NetBEUI- File And Printer Sharing- Client for MS Networks- IPX/SPX- File And Printer Sharing- Client for Netware Networks

• Other Adapter:

  - TCP/IP

  - File And Printer Sharing- Client for MS Networks- Client for Netware Networks- NetBEUI- File And Printer Sharing- Client for MS Networks- IPX/SPX- File And Printer Sharing- Client for Netware Networks

So what you need to do is unbind File And Printer Sharing, Client for MS Networks, (and possibly) Client for Netware Networks from the TCP/IP, NetBEUI, and IPX/SPX that are under Inet Adapter. Once

that is done, the IPC$ share should NOT be accessible from the internet, but will be accessible from your internal network. For more info, check out https://grc.com/x/ne.dll?bh0bkyd2 -- a site called Shields UP, which deals with this sort of stuff.

Natman

Excellent, Natman!

I would like to add a few references of my own. There is a good amount of information available on TechNet's website. Here are a few links to get you started:

• TechNet Web Site Security (https://www.microsoft.com/technet/itsolutions/howto/sechow.mspx)

• Secure Internet Information Services 5 Checklist

• MS Internet Information Server 4.0 Security Checklist

• Untangling Web Security: Getting the Most from IIS Security

• Knowledge Base—**Q229694:**How to Use the IIS Security "What If" Tool

Querying Index Server via Active Server Pages

Q: And to wrap up this edition of Top Questions, we present a good one from IT Guy Matt Goldstein. Here is his quandary:

Hello. I am writing my own set of queries of Index Server in ASP, not using the HTM/IDQ/HTX approach. I'm looking to do partial searches, where a whole word match is not required (e.g., searching for "men" would return men, women, mentor, lament, etc.), but it seems by default, that Index Server only returns "men."

Maybe that's a bad choice of words, but I digress. :-)

Anyhow, in regular SQL Server, I could simply say

SELECT * FROM wherever WHERE content LIKE '%men%'

Any idea how I can accomplish a partial match in Index Server? Any help would be greatly appreciated.

A: Matt,

You need to find out about Dialect1 and Dialect2 wild cards, and also there are similarity(?) flags that you can set. You want to get into the SQL Language Reference volume in the Indexing Server in the MSDN On-Line Library. The following page on my hosted web site has a link into the MSDN On-Line Library for the three major search "Methods"—one of which is SQL queries:

https://www.outlookbythesound.com/Lessons/L5_Index_Server/Search_Methods.htm

That should get you close enough to find the SQL Language Reference Volume.

Hollis D. Paul - MVP

We at Microsoft Corporation hope that the information in this work is valuable to you. Your use of the information contained in this work, however, is at your sole risk. All information in this work is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement, and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by Microsoft Corporation. Microsoft Corporation shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.