Tuesday, January 13, 2009

Linked Server SQL Syntax for SQL Server

Here they are some examples how to use linked server to execute SELECT, INSERT, UPDATE and DELETE statements. Run these statements in either Query Analyzer for SQL Server 7 / 2000 or in New Query field for SQL Server 2005 / 2008. These examples assume following conditions:

  1. "MYLINKEDSERVER" as the name for a linked server that connect to PostgresSQL Database using pgsqlODBC.
  2. BOOKS is a table in a schema named LIBRARY, and AUTHOR is a column in BOOKS table.

SELECT Statement

SELECT AUTHOR FROM MYLINKEDSERVER.LIBRARY.BOOKS

OR

SELECT * FROM OPENQUERY
(
MYLINKEDSERVER, 'SELECT AUTHOR FROM LIBRARY.BOOKS')

INSERT Statement

INSERT INTO MYLINKEDSERVER.LIBRARY.BOOKS (AUTHOR) VALUES ('Shaun Walker')

OR

INSERT INTO OPENQUERY (MYLINKEDSERVER, 'SELECT AUTHOR FROM LIBRARY.BOOKS')
VALUES ('Shaun Walker')

UPDATE Statement

UPDATE MYLINKEDSERVER.LIBRARY.BOOKS SET AUTHOR='Bill Gates' WHERE AUTHOR='Shaun
Walker'

OR

UPDATE OPENQUERY (MYLINKEDSERVER, 'SELECT AUTHOR FROM LIBRARY.BOOKS') SET
AUTHOR='Bill Gates' WHERE AUTHOR='Shaun Walker'

DELETE Statement

DELETE MYLINKEDSERVER.LIBRARY.BOOKS WHERE AUTHOR='Shaun Walker'

OR

DELETE OPENQUERY (MYLINKEDSERVER, 'SELECT AUTHOR FROM LIBRARY.BOOKS')
WHERE AUTHOR='Shaun Walker'

NOTE:

To perform DELETE or UPDATE operations, the target table must have a unique index. For more information, please refer to the section titled UPDATE and DELETE Requirements for OLE DB Providers in the SQL Server Books Online.

Monday, January 12, 2009

How to make Linked Server between MS SQL Server 2008 64 bit and PostgreSQL 8.3

If however you had a SQL Server 2008 64-bit server and wished to create a linked server to a PostgreSQL server. You ran into 2 very annoying obstacles.

Obstacle 1:
There for a long-time was no 64-bit ODBC driver nor native driver for PostgreSQL. This obstacle was somewhat alleviated when Fuurin Kazanbai made experimental compiled 64-bit PostgreSQL ODBC drivers available which work for AMD and Intel based processors.

Obstacle 2:
All looked good in the world until you tried this in SQL Server 2005 64-bit and low and behold - you needed a 64-bit OLEDB provider for ODBC to use it in SQL Server 2005 64-bit. Yes we waited patiently for years for this piece to be available. We still love you Microsoft. Then as Jeff Crumbley pointed out - Microsoft released an OLEDB 64-bit provider for ODBC in early April 2008.

Below are the steps to get a PostgreSQL linked server working in SQL Server 2008 64-bit:

  1. Run WindowsServer2003.WindowsXP-KB948459-v2-x64-ENU.exe - (Available as of 4/4/2008 from: http://www.microsoft.com/downloads/details.aspx?FamilyID=000364db-5e8b-44a8-b9be-ca44d18b059b&displaylang=en).
  2. Make the folder C:\Program Files\PostgreSQL\8.1\AMD64bin (you can try the 8.3 if you are running that) and place the dlls from psqlodbc_AMD64 available from http://www.geocities.jp/inocchichichi/psqlodbc/index.html.
  3. Run the psqlodbcwAMD64.reg file.
  4. Create a System DSN in the 64-bit Data Source (ODBC).
  5. Create a Linked Server in SQL Server to PostgreSQL (I'll explain it in detail below).

OK, now here are the steps to configure a Linked Server in SQL Server 2008:

  1. Launch the SQL Server 2008 Management Studio.
  2. Under the Server Objects folder, right click on Linked Servers and select New Linked Server.
  3. Enter a name for the Linked Server and choose "Other Data Source". Do not use any spaces or special characters in the Linked Server's name.
  4. Select "Microsoft OLE DB Provider for ODBC Drivers" from the Provider dropdown list. Enter MSDASQL for the Product Name.
  5. In the "Data Source" field, enter the name of the ODBC System data source you wish to use. Note that you have to use exact same name as it shown in the ODBC Data Source Administrator.
  6. In the Security page, select the "Be made using this security context". Enter a valid remote user/login (user ID) and password for the remote host.
  7. Leave the remaining fields blank and click OK to save the linked server configuration.

Now it's time for testing our configuration. If an error occures, review the error and make changes to the linked server configuration as necessary.

In the Management Studio, expand the linked server node and then expand the Catalogs node. Click on the node for the linked server and expand the Tables to view a table list.

That's it, and you've make a Linked Server between MS SQL Server 2008 64 bit and PostgreSQL 8.3. Congratulation. In the next posting, I'll explain how to use the linked server to manipulate the data in the remote server.

Thursday, January 8, 2009

Create MySQL User Account from Command Line

Follow these steps to create new user for MySQL in command line. Note this instruction assume that you have already setup MySQL.

1. Open a terminal and login to MySQL as root

mysql --user="root" --password="your_root_password"

2. Create new user and password with following command

CREATE USER 'new_username'@'localhost'
IDENTIFIED BY 'password_for_new_user';

3. Assign privileges to the new user

GRANT ALL ON *.* TO 'new_username'@'localhost';

4. Exit MySQL interface

exit

Notes:

The above instructions will create a new user account on "localhost" and grant the user all privileges. While this is safe for my development environment, you would obviously want to grant only the necessary permissions in a production environment.

For more information including the MySQL manual references, visit here.

Tuesday, December 30, 2008

Allowing application servers to relay off Exchange Server 2007

by: Scott Landry


From time to time, you need to allow an application server to relay off of your Exchange server. You might need to do this if you have a SharePoint, a CRM application like Dynamics, or a web site that sends emails to your employees or customers.

You might need to do this if you are getting the SMTP error message "550 5.7.1 Unable to relay"

The top rule is that you want to keep relay restricted as tightly as possible, even on servers that are not connected to the Internet. Usually this is done with authentication and/or restricting by IP address. Exchange 2003 provides the following relay restrictions on the SMTP VS:

Here are the equivalent options for how to configure this in Exchange 2007.

Allow all computers which successfully authenticate to relay, regardless of the list above

Like its predecessor, Exchange 2007 is configured to accept and relay email from hosts that authenticate by default. Both the "Default" and "Client" receive connectors are configured this way out of the box. Authenticating is the simplest method to submit messages, and preferred in many cases.

The Permissions Group that allows authenticated users to submit and relay is the "ExchangeUsers" group. The permissions that are granted with this permissions group are:

NT AUTHORITY\Authenticated Users {ms-Exch-SMTP-Submit}NT
AUTHORITY\Authenticated Users {ms-Exch-Accept-Headers-Routing}NT
AUTHORITY\Authenticated Users {ms-Exch-Bypass-Anti-Spam}NT
AUTHORITY\Authenticated Users {ms-Exch-SMTP-Accept-Any-Recipient}

The specific ACL that controls relay is the ms-Exch-SMTP-Accept-Any-Recipient.

Only the list below (specify IP address)

This option is for those who cannot authenticate with Exchange. The most common example of this is an application server that needs to be able to relay messages through Exchange.

First, start with a new custom receive connector. You can think of receive connectors as protocol listeners. The closest equivalent to Exchange 2003 is an SMTP Virtual Server. You must create a new one because you will want to scope the remote IP Address(es) that you will allow.


The next screen you must pay particular attention to is the "Remote Network settings". This is where you will specify the IP ranges of servers that will be allowed to submit mail. You definitely want to restrict this range down as much as you can. In this case, I want my two web servers, 192.168.2.55 & 192.168.2.56 to be allowed to relay.

The next step is to create the connector, and open the properties. Now you have two options, which I will present. The first option will probably be the most common.

Option 1: Make your new scoped connector an Externally Secured connector

This option is the most common option, and preferred in most situations where the application that is submitting will be submitting email to your internal users as well as relaying to the outside world.

Before you can perform this step, it is required that you enable the Exchange Servers permission group. Once in the properties, go to the Permissions Groups tab and select Exchange servers.

Next, continue to the authentication mechanisms page and add the "Externally secured" mechanism. What this means is that you have complete trust that the previously designated IP addresses will be trusted by your organization.


Caveat: If you do not perform these two steps in order, the GUI blocks you from continuing.

Do not use this setting lightly. You will be granting several rights including the ability to send on behalf of users in your organization, the ability to ResolveP2 (that is, make it so that the messages appear to be sent from within the organization rather than anonymously), bypass anti-spam, and bypass size limits. The default "Externally Secured" permissions are as follows:

MS Exchange\Externally Secured Servers {ms-Exch-SMTP-Accept-Authoritative-Domain}MS Exchange\Externally Secured Servers {ms-Exch-Bypass-Anti-Spam}

MS Exchange\Externally Secured Servers {ms-Exch-Bypass-Message-Size-Limit}

MS Exchange\Externally Secured Servers {ms-Exch-SMTP-Accept-Exch50}

MS Exchange\Externally Secured Servers {ms-Exch-Accept-Headers-Routing}

MS Exchange\Externally Secured Servers {ms-Exch-SMTP-Submit}

MS Exchange\Externally Secured Servers {ms-Exch-SMTP-Accept-Any-Recipient}

MS Exchange\Externally Secured Servers {ms-Exch-SMTP-Accept-Authentication-Flag}

MS Exchange\Externally Secured Servers {ms-Exch-SMTP-Accept-Any-Sender}

Basically you are telling Exchange to ignore internal security checks because you trust these servers. The nice thing about this option is that it is simple and grants the common rights that most people probably want.

Option 2: Grant the relay permission to Anonymous on your new scoped connector

This option grants the minimum amount of required privileges to the submitting application.
Taking the new scoped connector that you created, you have another option. You can simply grant the ms-Exch-SMTP-Accept-Any-Recipient permission to the anonymous account. Do this by first adding the Anonymous Permissions Group to the connector.

This grants the most common permissions to the anonymous account, but it does not grant the relay permission. This step must be done through the Exchange shell:

Get-ReceiveConnector "CRM Application" Add-ADPermission -User "NT
AUTHORITY\ANONYMOUS LOGON" -ExtendedRights "ms-Exch-SMTP-Accept-Any-Recipient"

In addition to being more difficult to complete, this step does not allow the anonymous account to bypass anti-spam, or ResolveP2.

Although it is completely different from the Exchange 2003 way of doing things, hopefully you find the new SMTP permissions model to be sensible.

Friday, December 26, 2008

Configuring the Hub Transport Server to Send and Receive SMTP Mail to and from the Internet

by: Thomas Shinder

The first thing that occurred to me was that we needed some way to send outbound SMTP messages to the Internet. That was pretty easy with Exchange 2003 and the SMTP service, because it is intuitive to think of configuring the SMTP service as sending and receiving SMTP mail. After all, that is what it is supposed to do.
After doing some searching through the Exchange Server Help file, I quickly found out that there is no such thing as an SMTP “service” on Exchange 2007. I do not know what they call the SMTP services now, but it is not the SMTP service. I guess calling it the SMTP service would have been too easy and Exchange Team realizes that the real fun in life is not in the having, but in the wishing. In order words, the real joy comes from the chase, not the catching.

From my investigations it appears that we need to create a Send Connector. Without one of these Send Connectors you will not be able to send mail to the Internet, so we better create one. Go to the Organization Configuration\Hub Transport node in the left pane for the console. Click on the Send Connectors tab in the middle pane of the console. Right click on an empty area in the middle pane and click New Send Connector.

Figure 1


On the New SMTP Send Connector page, put a name for the connector in the Name text box. In this example we will name it Internet bound Mail. From the Select the intended use for this Send connector drop down list, select Internet. Click Next.

Figure 2

On the Address Space page, click the Add button. In the Add Address Space dialog box, put an asterisk in the Domain text box. I put a checkmark in the Include all subdomains checkbox, although I do not know if this is required, since the wildcard tells the Exchange Server to send all mail to any domain through this SMTP connector (except for those domains that the Exchange Server is authoritative for) . Click OK in the Add Address Space dialog box.

Figure 3

You now see the wildcard domain listed and the type is smtp. Click Next.

Figure 4

We have several options to select from on the Network settings page. The default setting is to Use domain name system (DNS) “MX” records to route mail automatically. When this setting is enabled, it becomes the responsibility of the Exchange Server to look up the MX records for the destination domain and resolve those to an IP address.


Another option is to offload this responsibility to another SMTP server. When you offload the MX domain name resolution to another SMTP server, that other SMTP server is acting as a Smart Host. It is not uncommon to use the ISP’s SMTP server as a Smart Host. One of the advantages of this is that it is likely that there is a reverse DNS record for your ISP’s SMTP server, so you do not have to worry about reverse lookups at the destination DNS server. That is because the destination DNS server only looks at the name resolution for the last hop, not each hop the SMTP message may take on the way to its destination.


Another option on this page is to Use the External DNS Lookup settings on the transport server. By default, the Exchange Server will use the DNS server configured on its NIC to resolve the MX domain names. However, you might run an environment where you do not want internal machines to resolve Internet domain names (for example, you want the ISA Firewall to perform external name resolution, which forces clients to be Web proxy and/or Firewall clients). This is in general a more secure configuration and something recommended by world class security experts such as Tim Mullen (Thor).


When you select the Use the External DNS Lookup settings on the transport server option, you have the option to configure the SMTP “service” to use an alternate DNS server that can resolve Internet host names. In this way, you enable the SMTP “service” to resolve Internet MX domain names while preventing all other applications and services on the machine from resolving any Internet host name.


In this example, we will use the default setting, Use domain name system (DNS) “MX” records to route mail automatically. Click Next.

Figure 5

On the Source Server page, you will see that our Transport Server is already added to the list. Since we only have one, we do not need to add any more. Click Next.

Figure 6

Review the settings on the New Connector page and click the New button.

Figure 7

Click Finish on the Completion page.

Figure 8

Double click the Internet Bound Mail Send Connector that you created. This opens the Internet Bound Mail Properties dialog box. On the General tab, you need to make an entry in the Specify the FQDN this connector will provide in response to HELO or EHLO. This is an important setting if you are not using a Smart Host because the public IP address where outbound mail exits your organization must reverse resolve to the name you put here. If you are using a Smart Host this probably will not matter.

Figure 9

Click on the Server Configuration\Hub Transport node in the left pane of the Exchange console. Double click on the EXCH2007MB entry in the middle pane of the console.
In the EXHC2007 Properties dialog box, click on the External DNS Lookups tab. Here you have two options:

Use network card DNS settings This option allows you to use the DNS settings on the NIC installed on the Exchange Server. If you have multiple NICs on the Exchange Server, then you can choose the NIC from the drop down list. When you do so, you will see the DNS server address in the This adapter contains the following DNS server entries list.

Use these DNS servers - Choose this option when you want to use a DNS server that is not listed on any of the NICs installed on the Exchange Server.

In this example, we will use the DNS server address bound to the NIC, which is the IP address of the DNS server installed on the domain controller for this network.

Figure 10

There is one more thing we need to do to allow anonymous inbound connections to the Default receive connector on this Hub Transport Server. In general, you should not do this since you are allowing anonymous inbound connections to an Internet facing host located on the same security zone at the domain controller and mailbox server (in this example, the mailbox server is collocated on the Hub Transport Server). In a secure environment, you would use an inbound SMTP relay on an anonymous access DMZ. This could be an IIS SMTP server, or an Exchange Edge Server.

Double click on the Default EXCH2007MB Receive Connector in the middle pane on the Exchange Management console. On the Default EXCH2007MB Properties dialog box, click the Permission Groups tab. Put a checkmark in the Anonymous users checkbox and click OK.

Figure 11

The Future of Web Design - What is DotNetNuke®?

by: Lee Sykes

Have you discovered that within months your website becomes out of date and becomes a headache to maintain? DotNetNuke is the answer to your problems.

DotNetNuke® Overview

DotNetNuke® is a portal content management system (CMS) ideal for creating any type of website from commercial web sites, corporate intranets and extranets, online publishing portals, to a personal blog.
DotNetNuke® is provided free, as open-source software. It allows individuals to do whatever they wish with the application framework, both commercially and non-commercially.

The Static Website Problem

So you’ve created your static website using Dreamweaver, Frontpage, or programmed it manually in HTML?
Your next job is to promote your website and keep it up to date with lots of fresh content that the Search Engines love.
You soon discover that keeping your website up to date with the latest news and articles is a lot of work, within a few months your website becomes out of date and the site becomes a complete headache to maintain.

CMS – The Benefits

Using the DotNetNuke® Content Management system enables you to:
Login to your website from anywhere in the world and update your content live
Quickly and easily publish articles, images, documents, news, important information and more
Save Money – Lower Total Cost of Ownership (TCO) you no longer need to pay a webmaster to maintain your site
Support for an unlimited number of content contributors (ie. Give permission to members of staff to login and update certain sections of your website)
Easily change the look of your entire web site with just one click using skins
Extremely extensible admin interface to add and move content, panels and more.
Security - Every element of your website can have specific security settings applied to allow or deny users from seeing, editing, or administering the different parts of your site
Once you have used DotNetNuke®, you will not look back - your old web design programmes will gather dust!

What do you need for DotNetNuke®?

To run DotNetNuke® you need to use a web hosting package that provides ASP.NET and a Microsoft SQL-Server database.
The DotNetNuke® Community
An advantage with DotNetNuke® being open-source software is the vast number of users out there. There are many developers contributing in the form of improving the DotNetNuke® core code and ‘modules’ which provide extra functions to your website, such as photo galleries. There are designers creating ‘skins’ which you can use to change the graphics of your website, and there is also a central forum area where you can ask DotNetNuke® related questions.
This community is rapidly growing by the day and it demonstrates how DotNetNuke® is the web of the future.

Where do you get DotNetNuke®?

You can download the DotNetNuke® code for free from: http://www.dotnetnuke.com/ simply register with the website to gain access to the downloads.
How do I learn DotNetNuke®?
There are many resources out there supplying documentation, videos and tips from installation and programming, to web design using DotNetNuke®.
You do not need to be a programmer to use DotNetNuke®; the hardest stage is installing DotNetNuke®. This is just a matter of uploading the correct files to your hosting provider and setting up your SQL database. DotNetNuke® will then automatically install when you open your website.

All you now have to do is add the content!