?
Solved

Urgent Crystal Report ODBC SQL 2005 connection problem

Posted on 2012-09-18
9
Medium Priority
?
1,646 Views
Last Modified: 2012-11-12
Hello all Experts...

I am facing a serious problem with Crystal Report using ODBC connection to access SQL server 2005.

I have written a VB.net 2003  application which will call the Crystal Report object to display a report.
When the crystal report object was called, it will then connection to a remote SQL 2005 server using the ODBC which already setup on the client machine.
The ODBC is using "sa" account to login to SQL 2005 with namedpipe connection.

The client machine is under a domain login, with the domain user account having domain admin rights enabled.

Everything works fine in the above setting.

However this also give a risk that this domain user can simply connection to the SQL server by using "windows authorization" mode (without any login needed!!!)  in SQL management studio from the client machine, and all the data from the whole database will also be visible to them!!!!!

Now I have tried to remove the domain admin rights from the client user account, but this make the report failed to connect when calling the ODBC, even through I am sure that the ODBC is set to be using "sa" login, but NOT windows authorization mode.

I have also tried to disable the right to connect the SQL for this domain user in SQL 2005, and it still having the same problem....ODBC will then failed to connect.

Can any expert here can help me out to solve this security problem ?

thanks,
Paul
0
Comment
Question by:Paulckw
9 Comments
 
LVL 5

Expert Comment

by:DavidMorrison
ID: 38408726
Hi Paul,

Firstly I'd like to mention that connection the application with "sa" is a massive security risk in its self and massive over kill. you should be able to create a sql login with the relevant permissions that the application needs without giving is free reign on the server.

So to answer your question, if the ODBC is truly using "SA" then disabling the perms for the windows account of the user should have no ill effect on your connection.

When you say removing the admin rights for this user, what do you mean? how did you go about this?
0
 

Author Comment

by:Paulckw
ID: 38408931
Dear David,

Thanks a lot for your fast reponse.
I will try to create another new sql login that only for the report to use and see what happen.
You are right that the ODBC only use "SA" to login, as you have 2 options only in there: windows auth mode and sql mixed mode.  
I am sure i chosen sql mixed mode and type in the "sa" and password in ODBC.
So i really dont know why it still got problem!!! seems not logical.

Now when the crystal report object is called in VB, the report object will then look for the "specified" ODBC name that I have preset in the crystal report object when the time creating the report under crystal report designer 10.

For the admin rights removal, i done by right click the user properties under the SQL Management studio -> security -> login
and then I can remove its member of  "domain admin" from the user.

Hope this provide enough information to you

Paul
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 38409456
Did you create a system or user DSN?

Did you give all users permissions to the ODBC connection?

We resolved some these issues by creating a SQL group that included all users.  WE limited the rights of this group to the views and stored procedures our application and reports used.  They had no rights on the tables directly.  We used OLE rather than ODBC as the connection method to the database.

mlmcc
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:Paulckw
ID: 38409563
Hello Mlmcc,
I use a system DSN. Will this affect anything?
And do you mean that I can create a SQL group under the windows server or under SQL server? (as the SQL server 2005 is installed on the windows server 2003)

and then change the connection data source from ODBC to OLE DB in crystal report object?

Actually what i want is that the report object no need to look for any external ODBC again when being call by the VB application to open

thanks,
Paul
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38409764
I use a system DSN. Will this affect anything?
It will if different users use the same computer. A user DSN is available only for the profile in which it was created.

And do you mean that I can create a SQL group under the windows server or under SQL server? (as the SQL server 2005 is installed on the windows server 2003)
If you have AD, then you can create a windows group and add all your windows users there (i.e. AD SQL Users). Then you can add this windows group as a "user" in SQL and assign the appropriate permissions, therefore granting access to all the members of the windows group.

and then change the connection data source from ODBC to OLE DB in crystal report object?
This could be done with or without adding the sql group. It would simplify the administration (avoid creating dsn in all your clients) and improve speed.

If the report is using the ODBC as a connection, you should be able to refresh it without a problem no matter what user executes it. If you remove the AD user and it throws an Access Denied exception, there's probably something wrong with the report setup. Are you sure you're not updating the connection string of the report at runtime?
0
 
LVL 21

Expert Comment

by:mastoo
ID: 38410173
Is this a 32 bit application on 32 bit windows?  Open the right odbc administrator tool (32 or 64 bit), go to the dsn you use, and use the "test" button to verify that it can connect as sa.  This narrows the focus of the problem.  Once that works we can consider the connection from your application.  If the test didn't connect, my first thought would be you didn't restart the sql server after setting it to mixed mode.  That change won't take effect until you restart.
0
 

Author Comment

by:Paulckw
ID: 38410264
Dear Mastoo,

Yes using 32 bit windows ODBC and tested successful connect to database.
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 1500 total points
ID: 38410674
The SQL group I was referring to was in MS SQL Server.  It has been a number of years(10+) but I think we just included the AD All Users or Everyone in the SQL Server group.

You should be able to modify the report through the set database location by creating an OLE connection to the database and updating the report to use it.

mlmcc
0
 

Author Comment

by:Paulckw
ID: 38414776
Hello mlmcc,

Tonight I have done some testing and finally come up with something:

I can now set the right of a user (who use Windows Auth mode to login) to read only of the database under SQL server. So now when the user use Windws Auth mode to login remotely by using Management Studio, it can only read the data.

However, problem still occur here because the user STILL can simply login to the SQL server by using Windows Auth mode(with Management Studio).....althought he can only read the data, but it still leave a risk for someone to stole the data.

Now...I have tried to "disable" that user account in SQL....but then the ODBC seems not working!!!!  When the crystal report viewer is opening, the ODBC will return error saying Login Fail !!!

I really can't understand why ODBC having this problem because I have already chosen NOT to use Windows Auth mode, but using SQL mixed mode with user "sa" to login in ODBC.

Why ODBC still need to check for the windows user account ?????
How can I get rid of that???

thx,
Paul
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question