SSWUGtv
With Stephen Wynkoop
What is dual-control, and why should you care? Patrick Townsend talks all about security on today’s show.
Watch the Show
SQL Authentication Questions
Today we have many different kinds of responses wrapped around SQL Authentication. Mark and Richard write in with solutions for Remote Server Authentication problem shared by John yesterday.
In my example of using stored procedures as a technique to restrict individual permissions inside a database, Dale brings up the question of using ORM engines with Stored Procedures.
Dale:
The Impact of Entity Framework on SQL Authentication
Have you discussed how the use of Entity Frameworks affect the security settings in SQL? From what I understand this model no longer uses the stored procedure approach to database updates. Instead, the Entity Framework object updates the database directly.
Editor:
Dale, this is a really good point. Entity Framework, NHibernate, and other ORM tools are not necessarily unable to use stored procedures. Most of these tools do not automatically implement those stored procedures, you will have to manually configure them.
If you find it useful to protect your schema from end user tools for some reason, stored procedures are not your only solution. You can use Views just as easily to hide the inner workings of your schema. Additionally, views can be used to perform vertical or horizontal partitioning, restricting certain columns or rows from access by schema. This can have great benefits in a database that is more open to end user access and tools. Using views, Entity Framework can automatically generate the CRUD code.
Linked Server Authentication Problem
Mark and Richard both write in with experience implementing linked server authentication with Active Directory.
Mark:
Concerning John’s authentication issue, if I am understanding correctly, his problem is authenticating against the AD through multiple hops. I might be over simplifying here, and I am certainly not a network guy, but I believe configuring the servers involved to use Kerberos (delegated) authentication should do the trick…Incidentally, delegated authentication is a more secure authentication mechanism in itself. If John needs further clarification on how to configure this, please feel free to put him in contact with me.
Richard:
We’ve run into this a few times in the past. The fix is a pain but it does work. Check out the following blog entry which I think describes it best:
Essentially, the Service Principal Name (SPN) needs to be set for the server and the service account and computer accounts need to be setup to be trusted for delegation. The link above also includes a link for the setSPN utility which will be needed as well.
Fred replies with a link to his blog where he writes in detail on the topic of SQL Authentication…
http://sqlconcept.com/2012/10/25/a-few-words-on-sql-server-security.
Thanks for all the comments sent to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Secrets to Taking Command of Your Own Performance Review
It’s natural to shy away from evaluations, judgment, risk of rejection and confrontations. A performance evaluation or performance review meeting with your manager certainly falls into this category. Most of us postpone or even avoid such conversations until it is too late. When we feel we are doing well, we don’t think we need to discuss it. When we feel we are not doing well, we avoid it. In both cases, we’re postponing the cure. The cure, of course, is to take command of your own performance and the management of your own career.
Featured White Paper(s)
Key Management in the Multi-Platform Environment
Written by Townsend Security
This White Paper discusses the challenges … (read more)