They have been around forever, but have you really ever implemented Application Roles in SQL Server? For that matter, do you really know what they are and how to use them? In this short tip, I hope to explain just that.

First off, we need to talk briefly about how application security can be implemented in SQL Server. There is some debate over which model is better and I am not endorsing any specific one here. Regardless of whether you use SQL Server Logins or Windows Authentication, you still have to decide whether an application will use a single login to access SQL Server (and all appropriate database objects), or allow each individual user to have their own login. They each have their very own sets of pros and cons, but I want to focus on a specific con of each user having their own login. The biggest issue is that each user login has access to your server and to one or more databases. Does the user need to delete data as part of their job? If so, they will have this right whether they log in via an application or directly to the server. Often, the application controls what can and cannot be deleted based on a set of business rules; these rules usually don’t exist on the SQL Server itself. In short, if each user has their own login, they can access SQL Server directly and potentially cause some damage.

This brings us to application roles. You create them and assign permissions to them just like regular database roles but you can’t put users in them. Instead, the goal of application role is to provide a best of both worlds scenario for application and user security. Here’s how they work. You set up each user to have an account on the SQL Server with practically no rights. All they should be able to do is login to the server and run a system stored procedure called sp_setapprole. This procedure accepts a couple parameters, including the name and password for the application role. Running sp_setapprole will immediately endow the user with all the permissions that you set up on the application role for the current session only.

So what does this mean for security? As long as the password for activating the application role is only known to the application, your users will not have any rights when they login to the SQL Server directly. In order to have the permissions they need, they will be required to use the application that knows the password and can unlock the permission for the application role. Now you can have SQL Server manage individual logins and still have a secure environment that uses the rules and filters in place within your applications.

Digg It

You ever find yourself with the need to copy SQL Server logins from one server to another? Maybe you are setting up a failover site, building a replacement server, setting up a reporting instance, or maybe you just want to backup the logins just in case. If you are using Windows Logins, this is a simple matter of scripting the login and applying it to the other server. Copying SQL Server Logins from one box to another is a bit trickier because SQL Server stores and manages the password. So just how do you copy the login and preserve the password? I am glad you asked.

Understanding Login Components

To successfully copy a login from one server to another, you will need to ensure that the copy has the same SID and password. The link between database users and logins is done with the logins SID, if this is different on the new server than any databases you copy over will contain orphaned users. To ensure that both the SID and the password are the same, Microsoft has written a stored procedure to aid in our transfer.

SP_HELP_REVLOGIN

SP_HELP_REVLOGIN is a stored procedure that will return a complete list of the logins that exists on you SQL Server in a script that can be run to recreate them. This script does not exist on your SQL Server by default, you must create with the code provided by Microsoft in KB article 918992 here http://support.microsoft.com/kb/918992/. Once you have create the procedures you can easily generate the create statements that allow you to copy your logins.

As an example, I created a new login on my local instance of SQL Server called SQLScript with a password of scriptme. Now I can run SP_HELP_REVLOGIN as follows:

sp_help_revlogin 'SQLScript'

RESULTS:

/* sp_help_revlogin script

** Generated Oct 30 2007 9:23AM on laptop1 */

-- Login: SQLScript

CREATE LOGIN [SQLScript] WITH PASSWORD = 0x0100B642C5A8BC6778ECE4710ED3DC8D70E0EA31B6DF6B122756 HASHED, SID = 0x80525EB475F8414FB32D627BB876F213, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

As you can see, I now have the syntax I need to recreate the login on another box. The SID will be forced to the same value and the passwords will match by virtue of this statement providing the hashed version of the password. If you need to copy all the logins, SQL Server and Windows Logins, you can run SP_HELP_REVLOGIN with no parameters.

Digg It

Welcome to another CSTechcast.com podcast for IT professionals. This week we interview Adam Shostack, author of The New School of Information Security about the essentials IT organizations need to establish to really do security right. In the news, PDF security holes are under increasing attack, Cisco is busy applying patches to its IOS software, solid state drives from Toshiba hit 256GB for netbooks, and Microsoft announces RTM status for Essential Business Server aimed at the mid-sized market and Windows HPC Server for the super high-end. A lack of great new business apps for smart phones and an overabundance of one-trick ponies gets "The Worst Tech Move of the Week", we take "A Closer Look" at areas of unnecessary tech spending, and blocking access to USB drives from Windows is "The Weekly Tech Tip".

Links to stories and sites discussed during the show:
Adobe PDF Reader Vulnerable, U.S. CERT Warns (InformationWeek)
Cisco releases bundle of router security patches (InfoWorld)
Toshiba Unveils 256 GB Drives For 'Netbooks' (InformationWeek)
Windows Essential Business Server (Microsoft)
Microsoft Takes Its Newest High-Performance Computing Platform to the Street (Microsoft)
Enterprise 2.0 Vendors need to get more serious about mobile (The Fast Forward Blog)

The Podcast Awards nomination period closes soon, so get your votes in for CS Techcast at podcastawards.com. If you want to follow us on the social web check out friendfeed.com/cstechcast or twitter.com/cstechcast. Otherwise, give us a ring or type up some feedback, all available at CSTechcast.com.

Link to the episode: http://www.cstechcast.com/home.aspx?Episode=44

- Eric Beehler (consortioservices.com/blog)

Digg It

Check out another podcast for IT pros at CSTechcast.com. This week John Kembel, CEO of HiveLive, gets us familiar with the ins and outs of getting a business to engage with customers through new social networks. Find more information on them at HiveLive.com. The news brings us a read on IT jobs during an uncertain economy, the hacking of Sarah Palin's e-mail, Apple finally addressing the DNS vulnerability, VMWare Virtual Center coming to the iPhone, and announcements from VMWorld on how to extend virtualization beyond the operating system. Investment bank's lack of real information in a world of technology gets "The Worst Tech Move of the Week", those who undervalue their IT staff get ripped six ways from Sunday in "The IT Pet Peeve", and "The Weekly Tech Tip" reviews the snapshot feature in Hyper-V.

Links to stories and sites discussed during the show:
Wall Street turmoil unlikely to KO IT industry (NetworkWorld)
Report: Legislator's son at center of Palin hack talk (InfoWorld)
Apple update finally fixes important DNS bug (InfoWorld)
VMware's VirtualCenter coming to Linux, iPhone (InfoWorld)
VMware chief says the OS is history (InfoWorld)

If you'd like to support CS Techcast, vote for us in the Podcast Awards. They are taking nominations until the end of the month, so get your vote in. We'd like any feedback you'd be willing to give. Contact information is up on the home page. This week we took some pictures, so those will be showing up on the web site as well. I hope you enjoy the show and keep coming back to CSTechcast.com.

Link to the episode: http://www.cstechcast.com/home.aspx?Episode=43

- Eric Beehler (consortioservices.com/blog)

Digg It

Another fine podcast for IT professionals found here at CSTechcast.com. This week we talk enterprise 2.0 with Ross Mayfield, social networking extraordinaire and Chairman, President, and co-founder of Socialtext. Find Ross' blog at ross.typepad.com and SocialText's offerings at Socialtext.com. In the news, possible privacy issues with the IE8 beta phoning home, Dell's pushing into the VM space with new blade servers and storage, the DOJ is questioning the Google-Yahoo ad deal, HP's building an OS of their own, and the LHC gets hacked. Apple's new BSOD causing iTunes 8 gets "The Worst Tech Move of the Week", we take "A Closer Look" at Yammer and the benefits and drawbacks of micro-blogging in the enterprise, and "The Weekly Tech Tip" talks about Core Config, a new utility for Windows Server 2008 Server Core configuration.

Links to stories and sites discussed during the show:
MS defends IE 'phone home' feature, clarifies privacy policy (InfoWorld)
Dell unwraps products designed for virtualization (InfoWorld)
Sandy Litvack, a dogged trustbuster in pursuit of Google (CNet)
Hackers deface LHC site, came close to turning off particle detector (ZDNet)
iTunes 8 causes Windows Vista problems (ZDNet)
TechCrunch50: Yammer Wins TechCrunch50 (PC Magazine)
Core Config Utility (Codeplex)

We're not just a podcast, check out our ramblings about random thoughts on the social sites twitter.com/cstechcast and friendfeed.com/cstechcast. We always welcome your feedback, so hit the voicemail, feedback page, or blog. All are available at CSTechcast.com. Thanks for listening.

Link to the episode: http://www.cstechcast.com/home.aspx?Episode=42

- Eric Beehler (consortioservices.com/blog)

Digg It

A new interview, tech news, and insight from the podcast for IT pros at CSTechcast.com. This week we talk about smartphone and mobile device security with Dan Dearing, Vice President of marketing at Trust Digital. Find out more about Trust Digital at trustdigital.com. In the news, we discuss a kaleidoscope of a patch from Microsoft, social networking for G Men, Dell shutting down factories of their once high-flying made-to-order operations, a six-core server chip from Intel, and a recall of overheating Sony Vaio laptops. Comcast's FCC countersuit gets "The Worst Tech Move of the Week", Chrome, Firefox, and IE8 start up the browser wars once again when we take "A Closer Look", and "The Weekly Tech Tip" delves into the NETSH command.

Links to stories and sites discussed during the show:
Upcoming Microsoft patch lineup could be 'massive,' says researcher (ComputerWorld)
CIA, FBI push 'Facebook for spies' (CNN)
Dell Plans to Sell Factories In Effort to Cut Costs (Wall Street Journal)
Intel ready to announce six-core chip (CNet)
Sony recalls 440,000 Vaio laptops (ZDNet)

Thanks for listening and remember to give us feedback at the blog, at the voicemail box, and at our email. All of these are available at our home page: CSTechcast.com. Keep coming back and bring your friends too. If you'd like to support our show, post a review on iTunes or on your favorite podcast directory.

Link to the episode: http://www.cstechcast.com/home.aspx?Episode=41

- Eric Beehler (consortioservices.com/blog)

Digg It

A new podcast for IT pros at CSTechcast.com is ready for you to download. Anil Desai, respected author, Microsoft MVP, and consultant, talks about the journey of being an independent technology consultant and the lessons learned. Find more on his web site anildesai.net. In the news, we talk the IT disaster recovery efforts in effect prompted by Hurricane Gustav, what jobs are more at risk to being outsourced, Google Apps are not getting much adoption in the enterprise, we discuss the new Cellular Seizure Investigation Stick, and the latest beta of Internet Explorer 8. Comcast's bit cap gets "The Worst Tech Move of the Week", hit the buzzer for our less than 5 minute game show "Know Your Tech", and check out SharePoint wiki permissions in "The Weekly Tech Tip".

Links to stories and sites discussed during the show:
New Orleans IT departments brace for Gustav (ComputerWorld)
IT workers hit hardest by offshore outsourcing, survey finds (ComputerWorld)
Google's tough sell to Corporate America (Fortune)
CSI Stick grabs data from cell phones (CNet)
Internet Explorer 8 beta 2 (ZDNet)

Keep up with CS Techcast on the social nets at twitter.com/cstechcast and friendfeed.com/cstechcast. Help us out by writing a review where you subscribe to our podcast, either on iTunes or your favorite podcast directory. We look forward to brining you more great podcasts at CSTechcast.com.

Link to the episode: http://www.cstechcast.com/home.aspx?Episode=40

- Eric Beehler (consortioservices.com/blog)

Digg It

CSTechcast.com has a great show available for subscription and download this week. We interview Ken Ledeen, author of Blown to Bits: Your Life, Liberty, and Happiness After the Digital Explosion, about the current status of privacy in the digital domain and how you can steer your IT organization around these new privacy pitfalls. The news brings stats of Vista service pack 1 adoption, a new massive Microsoft data center, a lawsuit for Apple and their 3G iPhone, DNS continues to be exposed, and Apache Tomcat faces a new security vulnerability. ISPs who haven't patched their DNS servers get "The Worst Tech Move of the Week", we argue the confusion around SSL certificates in "Point/Counterpoint", and Active Directory logon problems give fodder for "The Weekly Tech Tip".

Links to stories and sites discussed during the show:
Vista users rush for SP1; XP owners dawdle on SP3 (ComputerWorld)
Microsoft's $500M Iowa data center to use shipping containers (ComputerWorld)
iPhone 3G owner sues Apple over dropped calls, slow speeds (ComputerWorld)
Security expert: DNS attacks are happening (CNet)
Exploit code published for Apache Tomcat flaw (ZDNet)

We have awarded our prize for feedback, but don't let that stop you. Submit feedback at our web site CSTechcast.com, at our blog ConsortioServices.com/blog, or at our Twitter.com/cstechcast and Friendfeed.com/cstechcast social networking locations. Let us know what you think and thanks for listening to CS Techcast.

Link to the episode: http://www.cstechcast.com/home.aspx?Episode=39

- Eric Beehler (consortioservices.com/blog)

Digg It

Let's do it again, another IT pro podcast posted at CSTechcast.com. This week we talk phishing threats and how to keep your users safe with Rohyt Belani, CEO of Intrepidus Group. See their new technology online at phishme.com. The news brings twelve new Microsoft updates for patch Tuesday, but Microsoft also tries harder with three new security programs, security concerns around the march towards virtualization, cloud entries from AT&T, others bring forth virtualization for small business, and economic woes hit IT jobs hard. Apple's iPhone kill switch gets "The Worst Tech Move of the Week", we take "A Closer Look" at the forthcoming Microsoft Essential Business Server 2008, and a strange hibernation feature in Windows Server 2008 brings us "The Weekly Tech Tip".

Links to stories discussed during the show:

Microsoft Patch Tuesday for August 2008: 12 bulletins (ArsTechnica)
Microsoft further commits to security, unveils 3 programs (Arstechnica)
Black Hat conference spotlights virtualization, DNS issues (InfoWorld)
AT&T Jumps Into Cloud Computing With Synaptic Hosting (InformationWeek)
Warily, Small Businesses Look To Cloud Computing (InformationWeek)
No Answers From Apple On iPhone 'Kill Switch' (InformationWeek)

We still want to give you a $25 Amazon.com gift certificate. All you have to do is submit some feedback. Drop by our home page, CSTechcast.com, for multiple ways to drop us a line. Keep your podcatcher pointed at CSTechcast.com for the best independent podcast for IT professionals.  Thanks to everyone for listening.

Link to the episode: http://www.cstechcast.com/home.aspx?Episode=37

- Eric Beehler (consortioservices.com/blog)

Digg It

Welcome to the podcast for IT pros at CSTechcast.com. This week we look at the coming trends for the SQL Server database platform with our friend Paul Nielsen, author of SQL Server 2005 Bible. Find Paul and his books at sqlserverbible.com. In the news; Apple's DNS patch fails to randomize ports plus other DNS patches show new flaws, IBM commits to the cloud with a heavy investment in data centers, Microsoft is set to deliver Small Business Server 2008 for mom and pops and Essential Business Server 2008 for the mid-market this year, the Storm worm pops back onto the radar with an FBI spoof, and Sun debuts JavaFX to compete with Adobe. Plus, Apple's culture of secrecy gets "The Worst Tech Move of the Week", and we put mobile security in our crosshairs for "A Closer Look".

Links to stories discussed during the show:
Apple's patch fails to fix DNS flaw, researchers claim (ComputerWorld)
DNS patches cause problems, developers admit (InfoWorld)
IBM Brings Cloud Computing To Earth With Massive New Data Centers (InformationWeek)
Windows Small/Essential Business Server RC1s arrive (Ars Technica)
FBI warns of new Storm worm attacks (ComputerWorld)
Jobs entrusts a NYT columnist with the truth about his health, even before he tells Apple shareholders (VentureBeat)
Travelers' Laptops May Be Detained At Border (Washington Post)

We apologize for the late post of our podcast, but system problems prevented a timely post. This is the first time we have missed the release mark. Anyway, we hope everything is back on track hardware wise. The drawing for an Amazon.com gift certificate is just a few weeks away, so visit CSTechcast.com to submit your feedback to enter. We'd like to thank those who have submitted the wonderful, constructive feedback so far and look forward to more from our listeners. Please subscribe and write a review on iTunes or your favorite podcast site. Thanks for listening.

Link to the episode: http://www.cstechcast.com/home.aspx?Episode=36

- Eric Beehler (consortioservices.com/blog)

Digg It

CSTechcast.com, your weekly source for tech, trends, news, and reviews for IT pros presents the latest episode of our podcast. Rhonda Layfield joins the fray to update us on the extensive deployment tools available for the Microsoft Windows platform. Find Rhonda contributing to the web site Minasi.com. Tech news brings everyone early exposure to the DNS flaw, VMWare decides to give away the ESXi hypervisor, Drizzle aims to slim down MySQL, the Brocade-Foundry marriage merges Fibre-SAN switching with 10G Ethernet expertise, and Terry Childs finally gives up the goods. Quick selling VC's are investing in "The Worst Tech Move of the Week", we take "A Closer Look" at virtualization sprawl, and we look at SharePoint disaster recovery in "The Weekly Tech Tip".

Links to stories discussed during the show:
New DNS exploit now in the wild and having a blast (ArsTechnica)
VMware Counters Microsoft, Will Make ESXi Hypervisor Free (InformationWeek)
Drizzle project plans a stripped-down MySQL (InfoWorld)
Dissecting the Brocade-Foundry Merger (eWeek)
SF mayor gets codes to hijacked city network (CNet News.com)
VCs Reap What They Sow (Gigaom)
The Silicon Valley VC Disease (Scobleizer)

Give us some feedback and win a $25 Amazon.com gift certificate. We really want to know what you think. Contact us from the feedback button, e-mail us, and post to the blog all at CSTechcast.com. Also find us micro-blogging at twitter.com/cstechcast and friendfeed.com/cstechcast. Subscribe so you never miss an episode.

Link to the episode: http://www.cstechcast.com/home.aspx?Episode=35

- Eric Beehler (consortioservices.com/blog)

Digg It