SQLServerCentral / SQL Server Security Skills / Testing Center

How can I unencrypted a stored procedure

Mai Nguyen-460836 — Wed, 05 Sep 2007 10:21:00 GMT

I got few stored procedures With Encryption for the database that I converted to SQL Server 2000. I need to access those stored procedures. How can I un-encrypted them?

Trigger Not Executing

vs.satheesh — Fri, 22 Jun 2012 01:04:14 GMT

Hi In My Organization we are planned to imlement a role based security.I created a one role . Role name is called "Developer".Under this role i created two users.I use follwing Statement.Step 1 : Create the Role CREATE ROLE [Evaluationrole]Step 2: GRANT insert to Evaluationrole GRANT update to Evaluationrole GRANT delete to EvaluationroleStep 3: Grant create stored Procedure to EvaluationroleGRANT EXECUTE TO EvaluationroleGRANT VIEW DEFINITION TO EvaluationroleStep 4: USE [master] GOCREATE LOGIN [dvsuser] WITH PASSWORD=N'dvsuser', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFFGOStep 5 : CREATE USER [dvsuser] FOR LOGIN [dvsuser] EXEC sp_addrolemember N'Evaluationrole', N'dvsuser'Here in this user Create stored Procedure and execute the stored procedure every thisk working file. but I am facing Problem in Trigger. For Example Any table I am having "Delete" trigger means, that time that table records not allow to delete the records. I am facing follwing Error The user does not have permission to perform this action. Please Give me a solution . I want this user execute the trigger also.Thank You...

Where is the test?

dan-572483 — Tue, 12 Jun 2012 14:49:49 GMT

The description of this section reads: "Discuss the SQL Server Security Skills test at http://www.sqlservercentral.com/testcenter"But there is nothing at that URL.

oracle views

alpeshchauhan — Wed, 06 Jun 2012 05:05:58 GMT

i haven't answers

SQL server free ebook.

youth.development.pleasanton — Wed, 08 Jun 2011 14:21:20 GMT

Can anyone give me a free ebook on SQL server? If possible then please give me a link from where I can download the ebook.Thanks for replies...........:-)

Sql 2005 express security

leoninicarlo — Sat, 19 Jun 2010 10:04:01 GMT

Hi, could somebody explain to me step by step how to attach these databases, I am going crazy.I am running Win 7, I downloaded the files but when I try to attach them I get error message 5 and 5123.It has to do with the new security which I cannot understand. If I take ownership of the Sql server, the Adventure works files and everything else involved I should be able to do it. No way, why the hell do they make it so difficult, at least they could have a good tutorial about it or are we supposed to become all scientist to run a stupid database.Thank you

sum all records over 35 weeks Stored procedure or SSRS

jbon007 — Fri, 16 Oct 2009 07:48:38 GMT

Dear All,I have stored procedure which shows records by week numbers and amongst everything else. When i place it in ssrs i display this in a chart by week number and patient volumes.However i i would like to display the chart upto 35 weeks and anything above sum all the records? is that doable in ssrs or do i need to state that in my stored procedure?regardsFarouq

Client server connectivity

SQL Kid-1137444 — Thu, 10 Sep 2009 09:12:11 GMT

have a client server application. Server password is changed every week. what are the different options to provide the new password to the client to connect to the server. please note that there ar multiple clients. Each client may or may not connect to the server every day.

timout elapsed error

vrijesh prajapati — Wed, 29 Jul 2009 01:54:14 GMT

Respected allWe are facing problem while fetching query to sql 2000 database from web application.ERROR:"The timeout period elapsed prior to obtaining a connection from the pool.this may have occured because all pooled connections were in use and max pool size was reached"Please provide solution.vrijesh

SA Lockout Err 15405 Cannot use special principal SA

E McNeill — Sat, 04 Nov 2006 20:27:00 GMT

Using sql Server 2005

Everything was working fine, then I got up one morning and everything was haywire. I am working on a web site and when I try to login in I get the error message Login failed for user 'SA' because the account is currently locked out.

I went to my SQL Server and was able to log in using my windows authentication.

1. My Server is set up for Sql Server and Windows Authentication mode

2. Most of the database permissions for sa were gone. When I tried to adder use SA to any database role I got the error message Cannot use the special Principal SA (Sql Server Error message 15405)

3. I logged on to the server as SA and got the same results listed above.

4. I don’t have a problem any other Sql Server logins permissions in my database – it only seems to be a problem with SA.

5. I have tried shutting down the server (Sql Server) and restarting it, but that didn’t fix the problem.

As I said before, this all seemed to have happened overnight. I am the only one using this server right now so I can’t figure out how these changes happened.

I am using the evaluation copy of Windows Server for Small business Server, Build 3790 with SP1. I have only been using it for 3 weeks so I am a long way from the end of the evaluation period, so I don’t think that is the problem.

Do you have an explanation or solution for this problem? Any feedback would be appreciated.

restricted user error

pramodsalgaonkar — Thu, 02 Jul 2009 00:59:48 GMT

i have installed sql server 2005 personal on win xp sp2 local system as admin user and want 2 use sql server for restricted user , but it is giving an error sql query analyser unable to connect to serverserver: msg 18456, level 16, state 1 login failed for user please help

Error 15405: Cannot use the reserved user or role name 'sa'

priyadevs — Tue, 07 Oct 2008 05:08:29 GMT

Error 15405: Cannot use the reserved user or role name 'sa'

What Is the Purpose of WITH ENCRYPTION?

slayrexx — Mon, 16 Jun 2008 15:04:24 GMT

Can someone please help me understand the usefulness of encrypting a stored procedure?

Windows AD Business Objects Single Sign on with passthru to SQL Server 2005 - How to Config

mphillips — Tue, 06 May 2008 11:52:24 GMT

If anyone has good links to a 'How to' that would be great.What I want to do is use Windows AD userid, password to enable Business Objects Webi / Crystal Reports access to SQL Server 2005 databases. Ideally without managing a BOE / CMS table list.thx,Mark

I can´t log on to epo orchestrator

nadia_nilsson — Sat, 12 Apr 2008 07:19:30 GMT

Yesterday the administrator just change the password for the main sesion at the server and now I can´t log on the console. The password ist attached to the console. please answer me. Thanks

Run as user in SQL 2000 errors in 2005.

sgtwilko — Fri, 15 Feb 2008 08:59:25 GMT

Hi,We are a software house that produces a product based on SQL server. We use a strong encryption for stored procs, functions, triggers and the like so our code can't changed or viewed.All procs are run as one user (from a website). This user has very limited permisions (eg, not able to get outside of the database and certainly not able to run xp_cmdShell).Inside several of these procs we have the following to change the user to one that is able to do more, and does have permission to run xp_cmdShell.exec OPENDATASOURCE('SQLOLEDB', 'User ID=Dave;Password=Dave').master.dbo.xp_cmdShell @cmdAs the proc is encrypted, and you are not able to view the password we believe this to be a secure way of controling permisions without limiting what the application does.This has been working fine in SQL 2000 for the last 6 years, this code does not work under SQL 2005 but only in triggers. We get the dreaded "Transaction Context in use by another session".We can't use 2005 specific features as 99% of our customers are running on 2000.Any ideas on this problem?Thanks,Ian.

Client string data altered - SQL 2000

steve1011 — Thu, 10 Jan 2008 14:58:31 GMT

Note to moderators: please move this post to appropriate category if this category was intended only for the Security Skills Exam discussions.2nd Edit: Okay, back to original claim. Further testing in php to develop a workaround indicates the problem is not with the php parser as the string is not being altered until after being passed to the db client or the interface to the db client. Edit: I just realized the problem might be related to the php parser considering 'backslash newline' as a line continuation sequence and removes it before calling the sql client. And, I just proved it. Odd that it would remove 'backslash newline' from the middle of a string, but it seems that it does. In php, this query, "insert unittest (teststring) values ('e\\\\\x0a\x0ae')" results in 'e\e', which is what I wanted. By adding an extra back slash before and new line after, the parser removes the inner set but leaves the outer set alone (thankfully). I am going to leave this post for the next person with simular problem. (note: the result string that I claim is what I want has a square box to indicate the new line character, but in order to show it in this post, I substituted a non-printable UTF-8 html escape sequence character.)Original Post:I have been security testing sql injection attack vectors from php application. Most articles note that single quote has to be escaped as two single quotes to prevent early termination of the input string, AND validate any other potentially dangerous character sequences -- but fail to mention what those 'potentially dangerous character sequences' (aka PDCS) are.I set up a unit test in php to brute force all character permutations to identify PDCS. I found odd behavior with dblib and ADO clients altering my test strings. It appears that the client libraries will respond to the backslash character as an escape character introducer, but only before the characters (found so far), 0x0a (LF), 0x0d (CR), 0x10 (DLE), and 0x3d (=).The test strings are 4 character permutations that range from 0-255. I've tested SQL2000 to insure varchar will correctly insert and select these strings without altering them. I use the default collation SQL_Latin1_General_CP1_CI_AS.In Query Analyzer the following TSQL inserts the string without error or alteration:schema: create table unittest (utID int IDENTITY, teststring varchar(10))declare @s varchar(10)set @s = 'e\'+char(10)+'e'insert unittest (teststring) values (@s)When using a client, the backslash and LF are stripped out of the string and only 'ee' is stored. I can store 'e\e' and 'e'+char(10)+'e' without error, but not 'e\'+char(10)+'e'.I set up DB-Library Options in the Client Network Utility with 'Automatic ANSI to OEM conversion' unchecked to avoid the client from altering some characters from 128-255.I've tried escaping the escape introducer (backslash) without success.I've also searched the internet for the last two weeks for any hint of what is occurring here and how to disable or hack around it. Got nothing.Any help or insight here will be much appreciated.

Problem adding the securityadmin role to the sa account (2005)

Chris-475469 — Thu, 09 Aug 2007 03:54:00 GMT

Hi Guys,

I'm trying to add the securityadmin server role to the sa user but have been unable to due to an error occuring. The error says "Add member failed for server role 'securityadmin'. Cannot use the special principal 'sa' Microsoft SQL Server error 15405."

I assumed that the sa account would already have the required permission to perform most operations on the server but I have been asked to try assiging this server role to sa by a support guy for our third party product.

Has anyone got any ideas why this error is occuring? I have been looking on loads of forums but cannot find anything relevant to me.

Thanks in advance for any help!

audit DML activities

rahul rathi — Sun, 08 Jul 2007 08:41:00 GMT

Hi,

I am new to sql server. I would like help from you gurus out their. I wanted to audit the activites (DML) on my database. I wanted the audit to achive the following

1] Audit specific tables for the DML activities.

2] capture the details of the username, time and system from which this query executed.

3] Can i get the changed and the previous values.

4] I don't want to use the triggers.

I did read in some articles this is achivable with the help of SQL-profiler and osql but don't know the exact steps. Could some one provideme with the step by step guide to get this done?

Please help,

Rahul.

Can someone help in preparing SOX security document for MS-SQL server production databases

Lina G — Thu, 05 Jul 2007 12:57:00 GMT

Hi iam anil, in process of preparing SOX security document for MS-SQL server which includes all the contents which are helpful for production database to be more consistent and can have track of all the changes done on it. I am looking for a document which can provide information that we are doing and to be done. Please send any checklist or some handy which can help the same. anil kumaranil.1004@gmail.com

Call Procedure in Visual Basic

Eric belair — Mon, 21 May 2007 19:32:00 GMT

Hi everybody,

i swear someone can help me here

i'm trying to call stored procedure......here is my code

' Connect to the data source using the Open

' method of an ADODB.Connection object..

Dim con As New ADODB.Connection

con.Open("provider = .NET Framework Data Provider for SQL Server")

con.string = "productid.mdf;Initial Catalog=Product;Integrated Security=True;Pooling=False;uid=uid;pwd=pwd "

' Create an ADODB.Command object that uses the

' connection object "con", and calls the stored

' procedure named "proc1"..

Dim cmd As New ADODB.Command

cmd.ActiveConnection = con

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "insertcustomer"

' Call the stored procedure..

cmd.Execute()

' Disconnect from the data source..

con.Close()

End Sub

and i have an error when i run it they error is

"Provider cannot be found. It may not be properly installed."

anyone here have isssue how to resolve this bug

Thanks you

User Access to sql server

Prem-321257 — Mon, 30 Apr 2007 13:24:00 GMT

Is there a way to identify users within SQL Server that have not accessed a database within 30 days?

Samuel

Trap Error

ashiknihal — Wed, 25 Apr 2007 04:29:00 GMT

How To trap error in SQL Server?

E.g :

While executing a procedure an error comes at first part of the procedure. So when error comes i don't want to execute first part, quit from there then second part of the procedure should be executed.

Normally when error comes subsequent statement will not be executed.How to handle this error.

Regards

Ashik Nihal M.s

Create Login with Select or Execute Permission

Mukesh-366255 — Mon, 16 Apr 2007 08:34:00 GMT

Hi All, I want to create login with following points

1) User can execute only SELECT statements from query analyser , no DML statements

Please help me for same.

Thanking you in advance.

database acess

shailesh-425946 — Thu, 12 Apr 2007 00:44:00 GMT

I have multiple databases created in sql2000 and my server is on a network where client machines also has their own sql server instances with databases and we all are using a standard username of "sa" and password :microsoft"

This allows all the users over the network to register each one's sql server on their own local machines and access all databases

Now I want to block certian users from accessing certain databases on my server, kindly anybody help me doing this?

ePo Orchestrator problem

Gesona Ngabo — Wed, 04 Apr 2007 07:37:00 GMT

Hello,New member I have a problem with my ePo Orchestrator console that I just installed, when I start it by clicking "log on to server" on the Global task list menu I get the following error message:The server is not responding. Please check the following1.Logon information is correct2.The network is running correctly3.ePolicy orchestrator server is runningNow when I checked with my administrator yesterday he told me that the database was in suspect mode, and he has been busy since then.Please assist as to what could be the problem.Gesona.

Difine Network User as Cube Server not in DOMAIN

Yudhi Hermawan-263574 — Sun, 21 Jan 2007 22:12:00 GMT

Dear AllI have OLAP Database Server and Web Server. both of server not in Domain.i have create local user in OLAP Server with role in SSAS. How to I configure User in OLAP Server Store in WebServer (user in WebServer have authority access role OLAP in OLAP Server)Thanks

SQl Server 2000 -- SSL Security Error

Lakshmi-398032 — Tue, 16 Jan 2007 15:07:00 GMT

I got a small problem with Sql Server 2000 which is installed on Windows XP.

I installed it long back and it used to work fine but suddenly I got the following problem when trying to open Enterprise Manger

“Error for the SQL Server:

A Connection could not established to (Local)

Reason: SSL Security error Connection Open (SECDoClienthandshake())…”

Tried a couple of things but no luck

Hopeso someone can help me!!!

Thanks

Lakshmi

2K5 data encryption across servers?

Joseph Uher — Tue, 16 Jan 2007 08:09:00 GMT

I'm exporing SQL 2K5 encryption for a password field; I've been successful. Now, a colleague has raised the question about replicating that database to (an)other server(s). Since the key will be replicated along with the database, there should be no problem with decrypting the data on the mirrored site, right?

SQL slow in Domain Control

antonia — Sat, 18 Nov 2006 05:37:00 GMT

Hello there, I need a help. I have a computer that has windows 2003 server installed and I have 9 clients in domain control that use SQL Active Directory, an aplications in Visual Basic. The problem is that whenever they try to use it is soooooooooooo slow and if I put the server out of domain control it works fine. So I think the problemcan be some configuration. I did look a lot and try few things but the problem persist. If anyone has some idea of what I could try or what can be the problem, I would appriciate a lot.

Thank You

Alessandro

EXECUTE permission denied on object ''sp_OACreate'', database ''master'', owner ''dbo''.

manoj kumar-381893 — Fri, 10 Nov 2006 11:27:00 GMT

[QUOTE=aish]I want to send email using sql stored procedure.my code is work fine in my local sqlserver account. when I use my online sql server it display this error.

EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'.

EXECUTE permission denied on object 'sp_OASetProperty', database 'master', owner 'dbo'.

EXECUTE permission denied on object 'sp_OAMethod', database 'master', owner 'dbo'.

EXECUTE permission denied on object 'sp_OADestroy', database 'master', owner 'dbo'.

How canI solove this problem?[/QUOTE]

Please let me do fastly.

Encryption & Decryption

Koneru Murli Krishna — Mon, 30 Oct 2006 11:52:00 GMT

Database Security, we are going to use AES 256 Symmetric Encryption. We will be using RSA for Asymmetric Key Encryption, 1024 Bits.

We got the code working for the seond case but for the first, WHEN:

CREATE SYMMETRIC KEY sym_Key WITH ALGORITHM = AES_256 ENCRYPTION BY ASYMMETRIC KEY asym_KeyGO

THEN:

-- Msg 15314, Level 16, State 1, Line 1-- Either no algorithm has been specified or the bitlength and the algorithm specified for the key are not available in this installation of Windows.

What can be the way out to be able to create the AES 256 Symmetric key.

SQL 2000 Vulnerability using SQL Server Management Studio

Lance-319244 — Wed, 25 Oct 2006 06:15:00 GMT

Hi, When using SQL Server Management Studio to connect to a SQL2000 database using a user/password that has only permissions for its database and default permission on the master database, it lists all the databases on the server, not a huge problem.Though if you right click your database and go to "Tasks" >> "Back Up..." now click the "Add" button under destination and in the "Select Database Destination" dialogue click the "..." button. You are now able to browse the entire drives file stucture.You are also able to overwrite other backup files or restore other backup files from any other database.If I do this with Enterprise Manager I get the following error : error 229: EXECUTE permission denied on object 'xp_availablemedia', database 'master', owner 'dbo'And with Enterprise Manager I only see a list of databases I have access to.Anybody got any suggestions on how to make my SQL2000 servers more secure?

Allocating Securables

liz madden — Wed, 13 Sep 2006 08:50:00 GMT

I am using Microsoft SQL Server Management Studio 9.00.1399.00

I have a very simple problem (hopefully easily resolved!)

I created a role and allocated securables against it.

When I go back into the role properties the securables that I previously allocated do not show up.

Does anyone know what I need to do ?

regards

Liz

Hi There

Satheesh M.Unni — Wed, 11 Oct 2006 01:30:00 GMT

New to this forum....

Hi to all..

Rgds

Satheesh

Security of the database

Mathew Daniel — Mon, 11 Sep 2006 01:49:00 GMT

Lot of issues of security have been discussed in the past. But a trivial problem of security baffles me.It is possible for anybody to copy the mdf and ldf files of a database, copy it on another machine and have complete access over it, if he has admin rights. How is it possible to overcome this security problem. What I need is once the database is created with a user on a particular machine, the access must be restricted to that user only and not even to sa. Is this kind of security possible in SQL Server 2000

Database access through SRS Datasource returning rsErrorOpeningConnection

Brian Sivel — Thu, 03 Aug 2006 09:48:00 GMT

I have report security setup and all users are members of a role that grants security to all necessary DB objects. However, 3 out of a group of 20 or more users are gettting the following error:

An error has occurred during report processing. (rsProcessingAborted) Get Online Help Cannot create a connection to data source 'ORTHO1'. (rsErrorOpeningConnection) Get Online Help Cannot open database requested in login 'ORTHO'. Login fails. Login failed for user 'domain\user'

The failure is not object related it is database related. Database name = 'ORTHO'

Has anyone come across this problem before?

Thanks for any help in advance...

bSivel

Great Free software to document your entire IT infrastructure

dab cutter — Mon, 31 Jul 2006 08:09:00 GMT

Hey guys my name is Dan Cutter from Ecora Software. I just wanted to let you guys know that we just released this great new free software that lets you document your entire IT infrastructure. This software does not expire after 30 days so you can use it as long as you want, as often as your want, absolutly free. I highly encourage you guys to check it out. Thanks

http://www.ecora.com/ecora/products/default.asp

Help with SQL 2005.

Vikas-94496 — Tue, 25 Jul 2006 22:41:00 GMT

Moved under SQL 2005 Administration

Database Security in 2005

Vikas-94496 — Thu, 20 Jul 2006 00:51:00 GMT

We want to set up Database Security for all our TEST Databases. We need users to have Read/write access to All tables,Be able to Create Stored Procedures, Create Views,Create UDF, View Diagrams. We have Granted them access to db_datareader,db_datawriter and Granted the Create privilages seperately. The problem is with these permissions they can't Alter the objects they just created. By Default All Developers are members of dbo schema. If i Grant them Alter permissions to the schema then they can alter the Table definations. This is something we don't want them to do.We can't have Developers to have a seperate schema because lot of code has been migrated from 2000 and is embedded with dbo. and the fact is we don't understand the schema culture very well.Any suggessions would be helpful.