SQL Server Security Forum

Table Deletion in a Database

Fri, 27 Nov 2009 10:03:53 Z

Hi Experts,
I'm using SQL Server 2005...
In my Database, some tables are getting deleted automatically....
Please advise me to Secure my Database such that Users to be given rights to edit the Database tables/fields but they should not able to delete a table...

Waiting for your valuable reply...

Regards,
Viswa Bala

Peter, I HAVE SAME, EXACT ISSUE WITH SQL SERVER.......How did you resolve it?

Sat, 31 Oct 2009 14:14:47 Z

Security Update for SQL Server 2005 Service Pack 3 (KB970892)

Installation date: ‎10/‎31/‎2009 3:02 AM

Installation status: Failed

Error details: Code 737D

Update type: Important

A security issue has been identified in the SQL Server 2005 Service Pack 3 that could allow an attacker to compromise your system and gain control over it. You can help protect your computer by installing this update from Microsoft. After you install this item, you may have to restart your computer.

More information:
http://support.microsoft.com/kb/970892

Help and Support:
http://support.microsoft.com

TDE and Clustering

Thu, 26 Nov 2009 19:54:54 Z

Is there any issue with using TDE along with SQL Server clustering? In particular, if we encrypt a database in one of the SQL Server clusered nodes, do we also need to encrypt it in all other nodes? If so, I'd imagine that we would need to use the same key and encryption algorithm?

Security Update for SQL Server 2005 Service Pack 2 (KB970895) - Error Code: 0x733F -

Fri, 27 Nov 2009 05:17:23 Z

Thie above update fails to install at every attempt. I've looked around and found others with the same problem, but many of the "solutions" are way too technical for me. I've been asked to post the "summary.txt" file from the hotfix folder in this forum, and it appears below. Please help.

Time: 11/27/2009 14:51:37.449
KB Number: KB970895
Machine: ASUS
OS Version: Microsoft Windows XP Professional Service Pack 3 (Build 2600)
Package Language: 1033 (ENU)
Package Platform: x86
Package SP Level: 2
Package Version: 3080
Command-line parameters specified:
/quiet
/allinstances
Cluster Installation: No

**********************************************************************************
Prerequisites Check & Status
SQLSupport: Passed

**********************************************************************************
Products Detected                         Language Level Patch Level       Platform Edition
SQL Server Database Services 2005 (SQLEXPRESS) ENU       SP2    2005.090.3077.00 x86       EXPRESS
SQL Server Tools and Workstation Components 2005 ENU       SP3           9.3.4035 x86       EXPRESS

**********************************************************************************
Products Disqualified & Reason
Product Reason
SQL Server Tools and Workstation Components 2005 The product instance SQL Tools has had update 4035 applied. You cannot install GDR update 3080 overtop update 4035. An update of build equal to or greater than 3353 should be downloaded.

**********************************************************************************
Processes Locking Files
Process Name Feature Type User Name PID

**********************************************************************************
Product Installation Status
Product                   : SQL Server Database Services 2005 (SQLEXPRESS)
Product Version (Previous): 3077
Product Version (Final)   :
Status                    : Failure
Log File                  : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB970895_sqlrun_sql.msp.log
SQL Express Features      :
Error Number              : 29503
Error Description         : MSP Error: 29503 The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, "How to: View SQL Server 2005 Setup Log Files" and "Starting SQL Server Manually."
----------------------------------------------------------------------------------
Product                   : SQL Server Tools and Workstation Components 2005
Product Version (Previous): 4035
Product Version (Final)   :
Status                    : NA
Log File                  :
SQL Express Features      :
Error Description         : The product instance SQL Tools has had update 4035 applied. You cannot install GDR update 3080 overtop update 4035. An update of build equal to or greater than 3353 should be downloaded.
----------------------------------------------------------------------------------

**********************************************************************************
Summary
One or more products failed to install, see above for details
Exit Code Returned: 29503

sql server 2000 authenticates with SERVERNAME$ account ion backup server instead of domain account

Mon, 23 Nov 2009 18:58:35 Z

sql server 2000. multiple servers getting access denied os error 5 accessing network backup share on backup server (lets call it \\backup\sql$\backupsgohere for now )
sql server is running under domain account, so does sql agent. they both are given sysadmin
for some reason sql server when it runs the backup job does not authenticate with the service account it is running under. audit on the share showed in security event log that sql server came with SERVERNAME$ account instead of domain account. another server for some reason came with a different domain account (that it used to be running under... not now so).
sql servers in question did restart when their service accounts were changed.

did anyone see such a case when sql server does not authenticate with domain account it is running under?

thanks

Feedback requested: Default schemas for Windows groups

Fri, 20 Feb 2009 01:43:50 Z

We want to seek your feedback on understanding the scenarios where you need to assign default schemas to Windows groups. Based on the requests we have received and reading through one of the longest threads on this issue (http://social.msdn.microsoft.com/forums/en-US/sqlsecurity/thread/7d46a024-7ed5-4c9b-b091-3640dc04f5a1/), it seems there are multiple solutions that people are trying to achieve using default schemas:

- Have a default schema as part of the user’s session that is set up by the application (through something like SET DEFAULT_SCHEMA = <foo>). Any objects referenced or created will be bound to that schema.

- Have a setting in SQL where it rejects any object reference without a schema. This enforces the app to always use 2 part names (best practice). This works best when you have access to the application source code or are writing a new application.

- Have the ability for a DBA to specify a default schema on a Windows group so that legacy applications can continue to work and reference the correct objects, without requiring all the users to be provisioned.

Does your scenario fall in another bucket other than the ones listed above? If so, can you please explain your scenario where this would be helpful?

Secondly, if you really prefer the third case, how would you handle cases where a user could belong to multiple Windows groups? What schema should the user be associated with in this case when each group has a unique default schema?

Looking forward to your responses

Sameer Tejani
SQL Server Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

Cannot connect Access 2007 to Sql server 2008

Thu, 26 Nov 2009 01:36:07 Z

I have a adp in Access 2007 SP2 that I'm trying to connect to Sql server 2008. On the datalink properties window I put the server name, user and password, but when I click on the dropdown to select the database I get: "Unspecified error" and then "Login failed. Catalog information cannot be retrieved.".

Anything I need to make sure it's enabled or configured on the sql server?

Any help is appreciated!
Thanks!

EncryptByKey Not Working

Wed, 25 Nov 2009 22:06:07 Z

when trying to update field EncryptByKey is retuning null
encryption & deryption works only when using variables
i checked the key name, field types still doesn't work
example EncryptedField should update to EncryptByKey return value of TextField
where EncryptedField is varbinary & TextField is nvarchar

SQL Server 2005
Vista Ultimate

Last SQL Server 2005 Update (KB970892) - does it break previous SQL command/code ?

Wed, 25 Nov 2009 16:38:34 Z

I'm wondering if anyone can help an increasinly depressed programmer/DBA find out exactly what might have been altered by the last round of server updates -- specifically the SQL Server 2005 (KB970892) from october 2009.
My netadmin gave me the link to the microsoft site, but I'm not used to looking at these and I can't figure out exactly WHAT was changed.
We had another issue where the update had affected some code that had run for several years. However, once we re-booted the server this past Monday, the issue was cleared up (for some reason, the server didn't re-boot after the last round of updates).

We have since (since 11/23) had 2 other instances of code that has been written & run for several years "breaking".
No edits have been made to the code.
One is using the command "WITH" in a stored procedure. The server croaks and fails the job, reporting an error around the word "WITH" -- but previous to Monday's reboot (where, presumably, the full affects of the october SQL Server 2005 service pack had not yet taken over) -- all was well....the code ran as it had for the last 3 years.
Now, it will not run.

We found a similar "break" in code that had been running -- admittedly, it was a non-standard (dumb!) way to write a query, but I had inadvertently copied/pasted the same field name into the query result string 2x. Since there was only a single table involved, there really was no "ambiguity" -- as far as the data coming from different tables -- and the code has run for at least 2 1/2 years.
After the re-boot of the server (after the last SQL Server 2005 update), the code croaked.

While both of these are (mostly) an easy fix -- my concern is that we've got a lot of code out there and it would be VERY handy to know specifically what changed -- so that I could find these (apparently) "non-standard" coding items that used to pass in SQL, but will now fail -- find them before all of the users find them and I have scads of "emergency" issues......

Since the only thing that has been reported (to me, at least) that has "changed" are the recent SQL Server/Security updates, I'm theorizing that these are what is causing this -- since they have mysteriously cropped up since the re-boot this past Monday....
but I'm certainly open to suggestions......
Thank you !

Kristi

Does bcp support SSL encryption? And how?

Wed, 25 Nov 2009 00:37:54 Z

I couldn't find out if bcp supports SSL encryption. The answer is NO to me. But maybe someone knows some tips.

Hama

SQL Server Agent Job - SSIS Package - Access Information required

Tue, 24 Nov 2009 15:07:14 Z

Hi Team,

Good Afternoon. We need some help in understanding the way the SQL Server Jobs work. Below mentioned is an incident that we have encountered recently.

Incident: SQL Server Jobs remains in the Execution mode for a very long time.

Description: As a standard all the services are setup to execute with the domain account by default. In this process, the SQL Server Agent Services was set up to execute with the domain account.

Also as a standard, a proxy account is created with the required credentials to execute SSIS package and OS commands.

New Job is created with the owner as [sa] to execute a SSIS package. This SSIS package is present on the file system (on disk). When this job is executed from the job, the SQL Server job doesn’t complete. It remains in execution mode forever. This happens very frequently across many servers. Initially the jobs execute without any issue but after some time we run into the problems that were mentioned.

When the package is executed from the command prompt with the same proxy account that was used in the SQL Server DB, SSIS package executes successfully without any issues.

One more analysis that we did is that change the service account from domain to local system account. When this is changed and when the package is executed, the job did complete from SQL Server Agent without issues.

Can anybody explain the reason for this strange behaviour? Also if in case if there are some other standards that we need to follow, please let us know.

Please let me know if I need to provide more information on this.

Many Thanks in advance.
Regards,
Siva

Creating a New User - Defaults

Mon, 23 Nov 2009 16:33:39 Z

In sql Server 2008, when you right click Login --> New Login --> Search: The object types that are set are: User or Built-in Security Principal. Is there way to have Groups as part of that default list? Is there a reason why this is not set by default? Are security is set to Mix Mode due to the requirements of 3rd party software that we can not alter.

Thanks

User can stll access DB without a login

Tue, 24 Nov 2009 17:45:04 Z

We have a user that we wanted to restrict to read-only access on a DB and deny creation of stored procedures. After playing around with the user's permissions, I eventually removed the login from the SQL server instance altogether, but the user can still connect. Anybody got any advice on the first step I should take to figuring out how this is even possible? We're on SQL server 2005 Standard Edition, and the user was logging in with Windows Authentication

Also, I tried to execute sp_helplogin, but I get an error saying that I don't have permissions to execute it, but I have full permissions on everything.

Thanks!

Clint

SQLSTATE 42000- Error 15404 with ADS

Tue, 07 Aug 2007 14:56:09 Z

Hello,

I'm having trouble running jobs with my active directory (ADS) account. I've setup my SQL services to run under an ADS account, but jobs cannot seem to query ADS for user information. We're running Windows Server 2003 and SQL Server 2005 SP2.

Here is the error message:

The job failed. Unable to determine if the owner (ADS\me) of job eFASRtest has server access (reason: Could not obtain information about Windows NT group/user 'ADS\me', error code 0x5. [SQLSTATE 42000] (Error 15404)).

also this message in log:

[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'ADS\me, error code 0x5. [SQLSTATE 42000] (ConnIsLoginSysAdmin)

I already tested the suggested:

execute as login='ads\me' and I get the same error on both (my local installations and production)

appreciate your help

Login Failed NT Authority\System

Tue, 24 Nov 2009 04:48:21 Z

Login Failed for NT Authority \System''

Error: 18456, Severity 14, State 16

When we ran the SQL trace unable to view the login failed details..

Advise needed...

Thanks

Naveen Mukkasa| Press Yes if the post is useful.

SQL Server Authentication account for backup purpose only.

Fri, 20 Nov 2009 16:39:19 Z

Hi,

In SQL Server 2005 SP3 server, I created a Maintenance Plan for backing up database to NAS. The job is working well through SQL Server agent.

We want to let CA-TNG to schedule the job. So, I created a SQL Server Authentication account "backup" and grant the role "db_backupoperator" from each databases and plus the role "db_dtsoperator" from database MSDB. The "backup" account does not have sysadmin server-wide fix-role.

The TNG will run the following DOS command to do the backup

dtexec /U backup /P xxxxxxx /SERVER MyServer /SQL "Maintenance Plans\Full_Backup_to_station" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /SET "\Package\Subplan_1.Disable";false /REPORTING E

I ran the command without problem but the TNG guy had permission problem.
After I check the evet log on the server "MyServer", I found out it has to logon to database with Windows Authentication account (domain\userid) first (it alway failed), then use the SQL Server authentication ("backup") later.

Then later, I add the domain userid into SQL Server Security login list without any permission just "public".
It still does not work. After I grant sysadmin role to this domain\userid on SQL Server, it works.

It is not my want. The role "db_backupoperator" looks meaningless because it has to logon as sysadmin first.

Any suggestion? Or what's wrong on my setting?

Thanks,
Xiaogang

Security problem when connecting to SQL from Windows 7

Wed, 14 Oct 2009 11:56:06 Z

Hello to all friends
I need to connect to a SQL Server 2005 from a Windows 7 machine that is not joined to the domain. This has always worked on XP but on Win7 I get the following error (when connecting via TCP/IP):
Login failed for user ''. The user is not associated with a trusted SQL Server connection.
I did not use IIS and Local program worked but when I install Windows 7, I did another successful ... unable to connect to database!
Thank's for all

Database Security Question

Wed, 26 Sep 2007 18:52:01 Z

I am running SQL 2005, SP2

I have two databases (happen to be SMS (dbA) and SMS Client Health (dbB))

I have created a view in dbA that selects records from a table in dbB - view is called v_ClientHealthStatus

The view works great with my credentials

dbA (the SMS db) has SMS Web Reports configured (IIS will run a particular report - which is a sql query). All reports work, except the one that tries to use the view, v_ClientHealthStatus. I get the following error:

An error occurred when the report was run. The details are as follows:

Verify Server encryption is being utilized

Thu, 19 Nov 2009 20:43:28 Z

I have a smart forms app that will access SQL Server 2005 accross internet, I have installed and configured SSl certificate, and checked force encryption in network config.

My question is how can I verify my ADO connectin to the application is truly using SSL encryption?

Thanks for any advice.

s.

Is it possible to configure SQL Management Studio so that SQL authentication connection is not available

Mon, 23 Nov 2009 14:04:38 Z

We have many third party suppliers providing databases - most of these will have created a SQL database user with full permission to the underlying data. Our business policy is that third party's should not make changes to live data.
The question is how to allow read only access to their databases when they all know the SQL authentication details for dbo user.

One possible solution could be as follows: we could allow them access to another server running with SQL Management Studio tools installed - they would have an AD account which would have read access to their database somewhere else on the network. If they were only able to use Management Studio through the Windows authentication mode then they would not be able to log in using the SQL authentication and then we would be able to enforce our policy.

Does anyone knwo how to do this - or any other solutions to this problem.

Security Update for SQL Server 2005 Service Pack 3 (KB970892) could not be installed

Fri, 16 Oct 2009 01:15:31 Z

Auto updates picked up this update to install but it wouldn't install. I tried doing it manually with no success. Anyone run across this?I have the service pack 2 with updates already. What would cause this?
Johnny

Granting a user rights to create procedures with SELECT and execute them, but not the base tables.

Tue, 27 Oct 2009 21:07:14 Z

We're using the latest SQL 2005 version.

I need to set some users up to be able to create stored procedures with READ access only. Also, in production they cannot have SELECT, only EXECUTE on the procs they created. In development they would have SELECT so they could create their procedures.

I've set up a schema called Reports. The owner of that schema is a login - Report_Admin. That user has select access to tables. I then gave alter and execute on the Reports schema to my report writer account. Dbo owns the table - so it works if dbo also owns the Reporting schema - but then a delete will also work in the procedure!

Is there a way to make this work?

If not, that's understandable, it's just nice to know there is no way data can be updated.

We are using reporting services and would like to have all the SQL in the database for maintainability.

Thanks!

SQL2005 permissions report

Mon, 23 Nov 2009 10:41:18 Z

I have been asked by our auditors to provide them with a report of the the effective permissions by login on a specific database. They want to make sure that only certain individuals are allowed to execute stored procedures or update values in a table. Is there a standard report, or alternatively what code can I execute?

Unlocking login on Status Tab always gets reset to Locked Out

Fri, 08 Sep 2006 15:04:38 Z

I've been having this issue lately with a new login. We have set it up to use the Windows Password and Expiration Policy and I give it an initial strong password. In testing this new account I would purposely input the wrong password into an application 3 times thus causing the login to become locked out. I would then go into the SQL Management Studio, go to the Status Page and uncheck the 'Login is locked out' flag. If I would then click the 'OK' button to close the properties, I would get an error message that the password is not complex enough. This is strange since I never changed the password in the first place, I just unchecked the 'Login is locked out' flag. That's issue#1.

I then decided that I would give Management Studio the benfit of the doubt and also change the password to something complex again while still keeping the 'Login is locked out' flag unchecked. I then could click 'OK' and not get any errors. If I then go back INTO Management Studio and go to the Status Page, 'Login is locked out' would be checked again! That's issue #2

Does anyone have ANY idea what the heck is going on here or what I am missing? I would think that I should be able to uncheck the locked out login and close without issue and have the end user login again with their old (existing) password.

Thanks in advance.

Scheduled job failure on linked Server

Wed, 18 Nov 2009 14:04:35 Z

Hi,

I have successfully created linked server from 2005 to 2000. I am able to see the table details of target server on executing exec sp_tables_ex.

but when I try to schedule a job to pull the data from linked server its throwing following error:

Executed as user: NT AUTHORITY\SYSTEM. Login failed for user '(null)'.
Reason: Not associated with a trusted SQL Server connection. [SQLSTATE 28000] (Error 18452)
TCP Provider: An existing connection was forcibly closed by the remote host.
[SQLSTATE 42000] (Error 10054) OLE DB provider "SQLNCLI" for linked server "PTUS01DS04" returned message "Communication link failure".
[SQLSTATE 01000] (Error 7412). The step failed.

Could someone help me in resolving this issue?

how to create linked server to named instance

Wed, 18 Nov 2009 15:10:30 Z

Hi,

I am trying to create a linked server for named instance through linked server wizard.

SQL SERVER 2005 to SQL Server 2005 Named instance

linked server details:
/*
Server name: myserver\myinstance
Provider: SQLNCLI
datasource: myserver\myinstance
product :SQL Server
*/

It was created, but when I try to run the Query it throughs an error.

Could any one provide me the steps to be followed to create a linked server to name instance??

Thanks in Advance.

15401 Error when adding AD User to SQL 2005 logins

Fri, 20 Nov 2009 17:50:22 Z

I have a very frustrating problem that I can't seem to figure out. Running SQL Server 2005 SP3 64-bit on Server 2008 R2.

When I first finished installing SQL I ran Surface Area Config to add DOMAIN\Administrator to the sysadmin role. After that I opened of Management Studio to add another domain user login (DOMAIN\OMAdmin) that was a brand new AD account. Adding the login, SQL finds it from AD if I search for it, but when I try to create it I get the 15401 error. I've followed every step in KB324321 and have had no luck. There are no duplicate SIDs, the SQL server can communicate with both domain controllers, SQL is not case-sensitive, name resolution seems to be working fine (I can ping the NetBIOS name of other servers and it translates to an IP just fine).

So I'm completely baffled. I have tried adding other domain users and they will not add either. Any help is much appreciated. Thanks in advance!

Linked Server via Windows Authentication

Tue, 17 Nov 2009 02:34:01 Z

Hi,

I've two SQL 2005 servers, A (mixed) and B (window auth). I want to create a linked server in A which can connect to B. A's SQL Service account is logged on using a local user account called A\sql_svc. I'm able to create the linked server using my domain ID, which has sysadmin to both A & B. However, if I run the job via SQL Agent, it gave me the below error :

Message
Executed as user: A\sql_svc. Access to the remote server is denied because no login-mapping exists. [SQLSTATE 42000] (Error 7416). The step failed.

What kind of credential should I use to create the linked server so that the job is able to run ?

Please enlighten.

TIA !

Hide all sys and INFORMATION_SCHEMA views from users in SQL server 2005

Mon, 09 Mar 2009 11:58:47 Z

We have a request from client to hide all system views/tables from users in SQL server 2005.

As user assigned to a specific database role, client do not want the user to see all system tables and INFORMATION_SCHEMA views, so they can have a clear view for only user tables in their schema.

However, whenever they connect using Access via ODBC they get a huge list of sys and INFORMATION_SCHEMA views.

Also when connecting from SQL Management Studio, they are getting same list.

We have taken following steps,but it does not worked.

1. DENY permissions on View Definition at all scope levels but still the users can see all these views using ODBC.

2. Tried denying access by changing permissions to deny in the public role, but still the same.

3. Created one Role including deny permissions to all sys and INFORMATION_SCHEMA views and assigned to user, but same issue.

Any suggestions.

Sivaprasad S

setting up read-only users. how to hide sys. tables. SQL SERVER 2005

Mon, 16 Oct 2006 13:15:23 Z

I need to set up 1 new user in SQL Server 2005 to be able to read specific tables in a db (db1).

The user will connect from MS access using odbc links (SQL Native client ot SQL Server driver)

I've tried to set up one and once logged on from the user workstation, I can only see sys. tables and INFORMATION_SCHEMA tables.
None of the required db1 tables appear.

under Security/Logins I've created User1:
SQL Server auth. with password
default db = db1
server_roles = none
user mapping = map, db1, user1,dbo
securables = none
status = grant, enabled

on the access db, the odbc link was set up with default db = db1

Why can't I see any of the db1 tables?

How can I restrict access to the sys. tables?

Thank you

Restricting Access to sys and INFORMATION_SCHEMA views in ODBC

Tue, 23 Oct 2007 08:55:37 Z

I'm building a data warehouse - my end users connect using Access via ODBC Microsoft SQL Server driver (2000.85.1117.00).

However, whenever they connect using Access via ODBC they get a huge list of sys and INFORMATION_SCHEMA views, in addition to the data warehouse tables they need to access.

How can I remove these sys and INFORMATION_SCHEMA views from the list of tables/views presented to the end user?

I've tried denying access by changing permissions to deny in the public role of the master database - I have also changed permissions in the public role in the data warehouse database. When I do this, the ODBC connection fails to retrieve any objects because it doesn't have access to sys.databases (and various other unspecified objects).

I'm stuck - help!

SQL Server 2005/2000 (JOB running through Domain level Id getting fail )

Sat, 07 Nov 2009 10:56:40 Z

Dear All,

In our production enviroment one new audit is implement from company pocily side.
all SQL Server aunthication account should be disable.

Now I have created 2 a domain level Id one foe sysadmin role & second for public role at SQL server level.
sysadmin id added in "Deney Intractive logon right" at windows level & "allow logon locally" aslo as per company policy.

when I try to connect with SQL Server level with sysadmin Id every time I have remove from "Deney Intractive logon right" ...

I am using runas command for connecting bec as per company policy I never connect with windows level with this Id.

please give me solution for checking the job at database level or how to connect from command prompt without removing from "Deney Intractive logon right" or how to connect remotely from GUI mode.

2. How to disable sa login in sql server 2000.

Regards
ravi

india

cross database permission

Tue, 17 Nov 2009 17:50:22 Z

dear all
I got a problem when I set up my database:

    database1 has a table called table1
    database1 has a user called user1 having no explicit permission with table1
    user1 is mapped to login1,which does not belong to any fix server role

    database2 has a stored procedure call sp2
    database2 has a user called user1 having EXECUTE permission with sp2
    database2 has a table called table2
    user1 in database2 has no explicit permission with table2
    user1 is mapped to login1 as well

I found:

it is successful,when I want to execute sp2 as:
select * from database2.dbo.table2

it failed, when I want to execute sp2 as:
select * from database1.dbo.table1

error message:The SELECT permission was denied on the object

I didn't grant user2 select permission, but execute the sp2, why it works?

when sp2 wants to select table1 in database1 why it doesn't work?

any idea. many thanks

cn2500

SQL Database Access Based On User Table?

Tue, 17 Nov 2009 16:35:44 Z

I am designing a database and am creating essentially a users table that gives access to the application. I'm wondering if there is a way to grant access to the database based on the security level assigned to users in the database's users table. So, for instance...

Users: Nathon, Dalton, 123 Some St, ..., SecurityLevel1 (Admin), ...
Users: John, Doe, 123 Some Other St., ..., SecurityLevel5 (DB Only), ... -- This would give access to the database only.

So, is there a way to setup the database so that it will allow/disallow people based on the security level in one of it's own tables?

Nathon Dalton .NET Software Developer

SQL Server

Thu, 30 Jul 2009 18:44:24 Z

sir,
i am running the sql server management 2009.
Currently having some problems relating to access to database. when i created the DSN file under ODBC, the test completed succesfully, using SQL Server authentication with user login and password. On the other side, At the SQL Enterprise Manager, I check the authentication method, and it is set to Windows and SQL server (Mixed mode).
while executing the database in dos mode using java it compile successfully but on executing it shows an error
it is
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa'
please help me
Amit kumar

SQL Server 6.5 to SQL Server 2005/2008 upgrade/migration.

Thu, 19 Nov 2009 20:49:02 Z

Hello,

We have a client with an app that currently uses a SQL Server 6.5 database with user passwords stored in the snefru hash which the users enter in the form they were generated, ie. PassWord even though sql server 6.5 would accept password, PASSWORD or any combination. The are wanting to go to SQL Server 2008 via an upgrade/migration but do not want to have to issue new passwords to everyone.

6.5 hash system is not supported in 2k5 or 2k8. Going to 2000, it cheats and hashes upper and lower cases.

If we were to aquire 7.0 and upgrade to that first and then go to 2k5/2k8, would that work? Apparently 2k5/2k8 will convert 7.0 hashes correctly but we do not know what 7.0 does to the 6.5 hashes, whether it treats them at 6.5 or actually converts them 7.0...

Any help would be appreciated, thanks.

Alec

Database restore & Symmetric keys

Thu, 19 Nov 2009 15:04:25 Z

Hi,

SQL Server 2005

Have a database which utilises a symmetric key / certificate to secure some of the database columns. I have a need to
move this to a new server but am running into problems when it comes to decrypting the data on the new server.

Steps I've taken so far

Backup database and restore to new server/instance.
Recompile all the procedures which use symmetric keys & certificates. By recompile I mean edit the code, add some whitespace and excute using alter ... .
Create the database master key using the same password
Create a certificate
Create a symmetric key based upon that certificate

The error i...

Msg 15581, Level 16, State 3, Procedure usp_GetFile, Line 6

Please create a master key in the database or open the master key in the session before performing this operation.

The stored procedure I'm running opens the symmetric key, see below and theis definately worked on the original server.

OPEN SYMMETRIC KEY TransfersKey

		DECRYPTION BY CERTIFICATE TransfersCertificate

Pretty sure I'm missing something really obvious.

Anyone got any ideas?

Thanks
Dave

Symmetric Encrypted by by a Assymetric stored in Master DB.

Thu, 19 Nov 2009 18:43:18 Z

I am in the process of encrypting various elements of our databases and I came across a situation that got me scratching my head. To start with, I have 2 production database servers with several DBs on them. Both servers have SQL Server 2008 Enterprise installed. A few of the DBs are mirrored from the primary server (ServerA) to the standby server (ServerB). I also have a HSM that will be used to store the keys used for TDE. I was able to get TDE and Database Mirroring to work without too many issues.

The issue that has arised involves encrypting columns in some of the tables. My goal would be to stored the keys for these encrypted columns in the HSM. Currently, the process to encrypt the columns is to create a Symmetric or Asymmetric key within the database. When you create these keys, you also encrypt these keys by some other process (i.e.. password, another key or by a key in the EKM provider). These is easily done using the EKM and the HSM, but the problem is when the database is mirrored.

When you use a HSM to enable TDE, an assymetric key is created in the Master DB, and is stored outside the database that is being encrypted. This AS key is then used to encrypt the database key of the database that is being encrypted. This very friendly to a mirror database because the AS key is stored in the Master DB and can be "reused" on another server by specify the "OPEN_EXISTING" option of the CREATE ASYMMETRIC KEY function on the other server. Unfortunately, this doesn't work well for keys that are being used to encrypt columns because the keys are created within the DB that is being mirrored. If you create a key using the HSM within the database that is mirrored to a standby server, the standby server does see the new key but it doesn't seem to recongized the fact that the new key being encrypted by a key in the HSM. When I attempt to decrypt the data in the column, on the primary server, the data comes back cleanly, but on the standby server, the data comes back NULL. There are no errors on the standby server when I decrypt the data either. So, this tells that standby server can see the symmetric key, but it can't decrypt the symmetric key because it dosn't "know" about the key in the HSM.

One option to overcome this situation is to use the other forms of encryption to encrypt the new key, which goes against my goals. Another way of possible way of encrypting a new key in a database with a key from the HSM, is to encrypt the new key with a key from another database, like Master DB. That way, I could run the CREATE ASYMMETRIC Key on the strandby server with the OPEN_EXISTING option and reuse the HSM key. So, is it possible to use a key in the Master DB to encrypt a key within a user DB? If not, could the SQL Server Development Team create a way? Or how can I get the standby server to "reuse" that HSM key that was firsted created on the primary server?

Thanks,
Dave Brown

关于通过存储过程跨数据库访问权限

Tue, 17 Nov 2009 18:01:35 Z

大家好, 我在设置用户权限的时候遇到了一个大问题, 希望大家帮忙解决一下!

问题描述:

1.同一Server有DB1 和DB2 两个数据库
2.DB1 里有 TB1(Table) , SP11(stored Procedure) SP12 (stored Procedure)
3.DB2 里 TB2(Table) , SP2(stored Procedure)
4.SP11 读取 TB1的数据 SP12 读取 TB1 和TB2的数据
5.SP2 读取 TB2的数据
6.用户名 U1 可以访问 DB1 和DB2 , 但只给了执行SP11, SP12 和SP2的权限, 不能直接对TABLE做任何操作
7.直接执行 SP11和SP2一切正常, 但当执行SP12的时候,系统提示TB2拒绝访问.

问题:

请问如何才能让SP12访问TB2, 我不想给用户直接访问表的权限, 只想让用户通过存储过程来操作表.

谢谢, 请各们指点~~~!

Access to MSSQL connection across domains

Mon, 16 Nov 2009 15:28:53 Z

Hi,

I have a problem I hope you are going to help me solve.

Sorry about the length of this post, but I thought it was worthwhile writing all the relevant things I could remember....

I have two domains that do not have a trust relationship between them and there is no prospect of being able to establish one. In one domain, the "client" domain, I have an MS Access DB that has been "upsized" (using the Wizard) to migrate the data to MSSQL. In the other domain (the server domain) I have the MSSQL server. All systems are Windows XP Pro, the MSSQL server is 2005 and the Access is 2003.

What I want to be able to do is connect to the MSSQL server from the client domain with the MSSQL server set to "Windows Authentication". I cannot use MSSQL authentication as this is not sufficiently "strong".

However, I have not been able to do this, despite the fact that I can map a network drive (that exists on the server) on the client system, using the SAME username and password that exists on the server domain.

The MSSQL has the user added as a user and has all the correct permissions, which has been tested by running the Access DB from a client within the server domain.

The Access upsize wizard migrates the Access tables to be ODBC linked tables, so I have been playing around with the connection string and have used ALL sensible combinations of the following options (including options not present):

DRIVER={sql server}; also tried {sql native driver}
DATABASE=<databasename>;
SERVER=<servername>;
Persist Security Info=True; also tried false
Trusted_Connection=no; also tried yes
UID=SERVER_DOMAIN\cpaterson;
UID=cpaterson;
PWD=apassword;

None of them work, where they don't throw up errors about invalid options the error that is logged at the MSSQL server is:

2009-11-13 11:46:05.43 Logon Error: 18452, Severity: 14, State: 1.
2009-11-13 11:46:05.43 Logon Login failed for user 'cpaterson'. The user is not associated with a trusted SQL Server connection. [CLIENT: 192.168.1.110]

I have even gone as far as trying ADO connections rather than the ODBC connections (which I think might mean I would have to rework my Access code considerably), but I get the same error.

If I enable Mixed mode Authentication (and use an appropriate username and password) it works with ODBC and with ADO.

Am I trying to do the impossible?

Why can I map a drive but not connect to the MSSQL server?

Any suggestion how to solve my problem or where else I might get help?

Regards

Callum

Error Number:	-2147467259
Source:	Microsoft OLE DB Provider for SQL Server
Native Error:	916