November 02 2011 How to give a user privileges on ALL databases in MSSQL
USE master
GO
DECLARE @LoginName varchar(256)
SET @LoginName ='joe'
SELECT 'USE [' + Name + ']'
+ ';'
+ 'CREATE USER [' + @LoginName + '] FOR LOGIN [' + @LoginName + '] WITH DEFAULT_SCHEMA =dbo'
+ ';'
+ 'EXEC sp_addrolemember ''db_datareader'', '''+ @LoginName + ''''
AS ScriptToExecute
FROM sys.databases
WHERE name NOT IN ('Master','tempdb','model','msdb') -- Avoid System Databases
AND (state_desc ='ONLINE') -- Avoid Offline Databases
AND (source_database_id Is Null) -- Avoid Database Snapshot
ORDER BY Name
Share on Facebook
Tags: SQL
September 22 2011 Exporting/Importing Mailboxes from Exchange 2007 using EMS
Many people fail to realize it but Exchange versions 2007 and newer come with a native utility to export and import PST files to/from your Exchange server. With previous versions you had to rely on third-party tools or use Microsoft’s EXMerge (which sucks, by the way…mostly because of the 2GB limit on their default ANSI PST file, but I digress…). The commands here aren’t case-sensitive but CamelCase makes them easier to read.
To export a PST file from Exchange:
Give yourself the necessary rights:
Add-MailboxPermission -Identity %username% -User %youradminusername% -AccessRights FullAccess
And then export the mailbox:
Export-Mailbox -Identity %username% -PSTFolderPath %exportpath%
To import a PST file in-to Exchange:
Give yourself the necessary rights:
Add-MailboxPermission -Identity %username% -User %youradminusername% -AccessRights FullAccess
And then import the mailbox:
Import-Mailbox -Identity %username% -PSTFolderPath C:\pstexport\%username%.pst
One important thing to know is that the default options for ‘Import-Mailbox’ DO NOT create duplicates. So if your import fails for some reason, you can just re-run the import command and it will skip everything that’s already in the destination mailbox.
You can also do a cross export/import between mailboxes/stores if you want (although doing moves between MDBs is easy through the EMC UI). You can read more about that on the Microsoft Exchange site. There are a ton of other import options too, like only importing certain date ranges, etc… but I won’t go in-to that here. More info for import options is here.
Share on FacebookTags: Server Management
September 13 2011 How to search all fields of all tables in MSSQL
Every developer has needed this at some point in time, especially when you’re dealing with a database schema that you didn’t create yourself. I found it here and am re-posting it for the sake of convenience.
CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN
-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
Tags: SQL
September 12 2011 PHP Function to strip everything except numbers from a string
function numeric_only($string)
{
//function to strip characters other than numerals from a string
$numString = ereg_replace("[^[:digit:]]", "", $string);
return $numString;
}
Share on Facebook
Tags: PHP
September 08 2011 How to determine storage utilization of a table in MSSQL
A handy little command to find out how many rows are within a table and how much space is being used by a single table in a MSSQL DB.
sp_spaceused ‘Tablename’
Share on FacebookTags: SQL
September 08 2011 Escaping special characters in PHP for MSSQL
PHP has a built-in function called mysql_real_escape_string() for use in MySQL queries but MSSQL has been left in the lurch for this one. I found this handy function on the StackOverflow forums that works like a charm (renamed for more uniform convention).
function mssql_escape_string($data) {
if ( !isset($data) or empty($data) ) return '';
if ( is_numeric($data) ) return $data;
$non_displayables = array(
'/%0[0-8bcef]/', // url encoded 00-08, 11, 12, 14, 15
'/%1[0-9a-f]/', // url encoded 16-31
'/[\x00-\x08]/', // 00-08
'/\x0b/', // 11
'/\x0c/', // 12
'/[\x0e-\x1f]/' // 14-31
);
foreach ( $non_displayables as $regex )
$data = preg_replace( $regex, '', $data );
$data = str_replace("'", "''", $data );
return $data;
}July 31 2010 Lessons learned from being a Systems Administrator for an ISP
Lessons learned from being a Systems Administrator for an ISP
As one of my first forays into entrepreneurship, I co-founded an Internet Service Provider with a friend of mine. I still own the ISP and somewhat run it (it’s no longer my “day” job but I still get to tinker with the infrastructure…I have a couple of really good employees that run the daily operations). Looking back, there are a lot of things that, if done differently, would have saved me many hours of headaches and lost sleep. I’ve attempted to compile a list of some of those items, both for my own future reference and hopefully to help anyone else that may be setting out to build or maintain a consumer oriented publicly-facing service provider network.
- Lock down outbound port 25 (SMTP) connections to only allow access to your own servers. If any of your access customers are using outside e-mail services, have them contact the admin for that mail provider or service; they likely offer an alternative port (such as 587 or 2525, or just enabling SSL for the SMTP connection). Locking down SMTP connections will help minimize the risk of spam from being sent from your network.
- Limit the number of emails that can be sent in a single day on a per-account basis. I used to leave this as ‘unlimited’ since I had a level of trust with my users that they wouldn’t be sending spam (I ran a very local ISP so I had met most of my customers face-to-face…which helps with trust). The problem I always ran into was that if someone’s computer got infected with a virus, it would send out a TON of spam or virus emails to propagate itself. If you set a limit relatively low (<250/day), it will be fine for most residential users. We’ve always had a a policy of increasing the limit to a much higher number if asked, but this helps eliminate problems due to our less savvy users getting their PCs infected. Note: our mail server software (Merak/Icewarp) allows for this setting to be controller per-user; some software may only allow for system-wide settings. In that case, you may have to start at a higher threshold if you have any business customers that use your mail server for commercial (but legitimate/non-spam) uses. I set our threshold incredibly low (100 messages per day) and only had to increase it for about 25 users (out of 3500).
- If you use BIND for your public DNS, DO turn off caching for all public IPs (that is: only answer queries for zones you’re authoritative for to “anonymous” requests but you can still answer recursive requests for “your” IP blocks).
- Enable SMTP authentication and allow an alternate port for access (due to what I mentioned in item #1 of this list). While you may primarily control SMTP relaying by allowing a certain range of IPs to send anonymously, you’ll inevitably have some users with a need to send emails while off-network.
- For your mail server: properly configure Reverse-DNS so that it matches the SMTP banner on your MTA and make sure there’s a matching Forward-DNS record for the public hostname/IP combination.
- Setup proper SPF records in your DNS. It only takes a few minutes and will greatly increase deliverability of your outbound mail.
- If you put your SMTP server behind a NAT’ed firewall, make sure that all outbound connections originate from the same public IP as inbound connections to it are on. In other words: don’t port-forward the SMTP port on a secondary IP of your firewall but allow the outbound connections on your mail server to just go out through the ‘default’ IP of your firewall. This will cause you a lot of grief when sending email to other organizations. It will also break your SPF configuration. Hint: search for 1-to-1 NAT in your router/firewall documentation.
- If you plan to host any SSL sites, place your DNS service on separate machines (i.e. don’t combine DNS and WWW services on the same box). I made the mistake of doing this and have had to do a bunch of firewall tricks to be PCI Compliant. In hindsight, putting DNS and WWW services on separate servers would have saved many hours of fiddling with settings on BIND and my firewall.
- When you setup a backup scheme for your databases (probably MySQL), set them up in a fashion so each database is stored in a separate dump file. Sifting through a >1GB mysqldump file to copy+paste the section that contains the tiny little database that your customer accidentally f’d up is no fun. Notepad doesn’t like it either.
- If your webmail interface uses SSL, spend the extra money and get a SSL certificate that matches the domain so your users aren’t prompted with a SSL certificate warning. Recent versions of IE and Firefox show the equivalent of a “doomsday warning” and will scare your users from using your webmail. I’ve had users call me to let me know that our server got hacked because they were so freaked out by the warnings. Thanks IE…..
- If your end-users are dial-up users, try like hell to implement per-message size limits that are less than 1MB…or steer your users to webmail if possible. I’ve been on more phone calls than I can remember, just trying to explain to ol’ Mrs Jones why she can’t download the 75MB high-resolution pictures of her grandchildren via POP3 on her 33.6k dial-up connection. Some people will understand that it’s just not possible to download these attachments over dialup….other people will blame you for disconnecting them from “the server” (Outlook and most other mail clients will time-out after 60 seconds of not receiving a complete message and show a generic “the server has disconnected” message). If you can steer them to webmail, it’s usually easier to view/download the messages since attachments can be downloaded 1 at a time…whereas POP3 has to get the entire message and ALL of the attachments as one single file.
- Stay away from mail servers that use the MBOX file format (or other single-file per-user data schemes). It may not be the case with all mail servers but I always ran into issues where the file would stay locked if a users session timed-out while opening/downloading a message….I always ended up having to kill the POP/IMAP process to unlock the MBOX file.
My ISP was/is primarily a Linux shop. I used the following when building the infrastructure:
- OS: Debian Linux – http://debian.org
- RADIUS (Authentication): FreeRADIUS – http://freeradius.org
- DNS: BIND – http://www.isc.org/software/bind
- Firewall: PFSense when NAT is used, IPTables when firewalling a Linux host
- SSH/VNC/Remote Desktop client: VisionApp Remote Desktop – This software is awesome; you can flip between SSH, RDP, VNC and Telnet sessions using tabs…just like switching between web-pages using tabs in Firefox.
- For servers that are low utilization (e.g. our Windows web server….I hated to set one up but some people swear by Frontpage): VMWare Server – http://www.vmware.com/products/server/
- Linux/LAMP Server: We compiled MySQL+Apache+PHP from source but it was a total pain. Since the ISP I’ve grown to love DirectAdmin (http://directadmin.com). You can still compile custom options into PHP if necessary but it gives your end-users/customers their own WebUI to manage aspects of their site themselves.
- FTP Server: if you decide to roll your own LAMP server (vs using something like DirectAdmin), I’d suggest VSFTP. It’s got everything you need for security built-in and it just works…probably the most maintenance-free service I manage.
- Web Server: Hands-down, Apache. Again, I would suggest just using DirectAdmin. It’s pretty easy to compile from source though (if you don’t need/want a control panel and are comfortable editing config files to manage sites).
- E-mail: Merak Mail Server, now known as IceWarp. I’d have to say I’m not a fan of this software anymore. It worked well for what we needed: it was stable, easy to administer, offers a good API for integration with our CRM and has decent webmail. my problem with it is/was that the spam filtering is somewhat of a black-box. I was never able to get decent support from the publisher of the software which meant that I had to find my own workarounds for dealing with spam that was “black holed” by the spam filters, even when the sender was whitelisted or the recipient had filtering turned completely off. In hindsight, I would recommend AtMail (http://atmail.com) – I’ve used it for other projects and it’s awesome (and Linux based!).
- Remote Support: GotoAssist Express – there’s a monthly fee, but it’s well worth it when you have to do remote support. I’ve also used R-Hub’s Turbomeeting appliance which is equally as awesome and a one-time purchase instead of a subscription.
- Webmail: Over the years, we went through Squirrelmail, DWMail, Roundcube, NeoMail, and eventually ended up just using the Icewarp interface since it was bundled with Merak and integrated the spam filters into each users’ webmail account. Of all of these, I would prefer/recommend Roundcube because it provides drag-and-drop and a fast-ish AJAX interface. Or AtMail :)
Tags: ISP, Server Management, System Admin
January 26 2010 How to disable the BEEP sound in Terminal Services
In the following scenario:
- Using Microsoft Terminal Services (Windows 2003 or 2008).
- Sound redirection disabled at the server or turned off at the client
Certain system events will still cause the system to ‘beep’ out of the system speaker. Turning off Windows Sound Schemes doesn’t stop the notification beep. Note this is sound is NOT out of the normal soundcard/external speakers but will be out of the internal speaker on the motherboard. If you’re having this problem, you’ll know how incredibly annoying it is. After hours of digging online and trying different scenarios, I stumbled across a fix and have documented it here.
On the terminal server, open Regedit and navigate to the following key:
DisableBeep Registry Entry
Tags: terminal server, terminal services, Win2008
January 17 2010 The quickest way to install Joomla 1.5 on a LAMP server
So I was going to setup a website for my wife’s new gig on my web-server. I created the MySQL database, FTP account and configured Apache accordingly. After downloading the Joomla 1.5.15 tarball and starting the FTP upload to my web-server, I went looking for the Joomla quickstart guide. To my astonishment, the “quickstart” guide is a full 49 pages long. Awesome, to say the least…and not too quick.
Here’s another one of those blog posts that’s mostly self-serving but hopefully helps a few other Joomla hopefuls.
To install Joomla 1.5 on a LAMP (Linux+Apache+MySQL+PHP) server:
- Download the tar or zip file from the www.joomla.org website
- FTP the contents of the file to your web server
- Browse to the URL of your web-server (the URL of where you uploaded it)
- Walk through the wizard, enter all information as needed.
- Once you’re at the ‘Congratulations’ page, reconnect via FTP and delete the ‘installation’ folder from the server.
- That’s it! You can now manage your Joomla site by going to the http://sitename.com/administrator address.
What’s great is that with the newer versions of Joomla, the admin console actually connects to your web-server using FTP to make changes to your config files and to upload Themes/Plugins, etc.. No more chmod’ing files on your web-server!
Share on FacebookTags: how-to, joomla installation, lamp
July 20 2009 Jailbreaking my iPhone 3G…not rocket science
I’m posting this here as a reference for myself and for anyone else who might be scouring the net looking for the easiest way to jailbreak their iPhone. I found some other guides but the utilities either crashed on my machine (Windows 7 beta), or the download links were broken. I watched this video, then downloaded the utils from his links, then watched again and jailbroke my phone while I followed his example. Worked like a charm….
Check out the video on YouTube
I’ve got some hacks that I’ve put together for a very specific purpose. I’m sure they’ll be useful to others but I can’t share them yet. I’ll post back here oneday when sharing them will be constructive :)
Share on FacebookTags: hack, jailbreak iphone
