March 21, 2009 at 4:40 am
Hi,
We've deployed an app, and the site admin has elected to use SQL security. He has reported that they are only able to run the app as accounts in the local admin group on the workstations (SQL is on its own server). We normally use windows security, so I've not run into this before. Is local admin a requirement? Here's the connection string:
Provider=SQLOLEDB.1; Password=????; Persist Security Info=True; User ID=AUser; Initial Catalog=TheDatabase; Data Source=TheServer;
Is there any way around this? Different connection string or something?
Thanks,
Kev
March 21, 2009 at 5:24 am
Oops, I've just been informed it's a VB6 app, not a .Net app!
March 21, 2009 at 11:12 am
VB6 may be the reason so your employer needs to either move the application to .NET or deploy in virtual machine, if your users are not many you could run such application in SQL Server Express instead of for pay edition. This reduces the risk of your instance security risks.
I am assuming you know as of .NET 2.0 Microsoft does not support previous versions of their languages before .NET because there are now free versions of Visual Studio.
Kind regards,
Gift Peddie
March 21, 2009 at 3:44 pm
What windows accounts you use it does not need to be local admin to access resources with in SQL Server.
A windows account can be granted connect permissions then further permissions to its database. I am fan of windows account over SQL accounts because of audit. When you use single SQL account for all connections you have no idea which user is doing what. When you create windows accounts for people (or windows group) you can see what people are doing.
If you have to use a single account; then only give access permissions to connect to server and connect to database. From there use application roles to activate permissions to limit access..
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 21, 2009 at 4:35 pm
Good luck with all that granular permissions in a very old COM based application because if the runtime does not allow all that then your users will tell you they cannot access SQL Server and the database. The reason to use SQL Server Express in virtual machine to isolate access.
Kind regards,
Gift Peddie
March 21, 2009 at 6:14 pm
Gift Peddie (3/21/2009)
VB6 may be the reason so your employer needs to either move the application to .NET or deploy in virtual machine,
.Net is on the cards, but the app works fine as it is, is out at quite a few sites, and requires little if any maintenance or further development, so porting has never become a priority
if your users are not many you could run such application in SQL Server Express instead of for pay edition. This reduces the risk of your instance security risks.
I'm not sure I follow - is there a need to use local admin accounts to run the client application against SQL 2005, but not against the express version? That seems a bit strange!
I am assuming you know as of .NET 2.0 Microsoft does not support previous versions of their languages before .NET because there are now free versions of Visual Studio.
See above - a visual studio license is by far the cheapest part of porting a reasonably sized application from VB6 to .Net. Not to mention the fact that we already have VS2008 team suite installed everywhere
Kev
March 21, 2009 at 6:21 pm
Mohit (3/21/2009)
A windows account can be granted connect permissions then further permissions to its database.
Yes, we generally recommend windows security, and this is in fact the first client that we know of who uses SQL security. Their reason is that they do not operate a domain. They also operate a password expiration policy, so simply creating matching accounts on the SQL server would require fairly regular changing of the passwords. A domain would solve their problems, but we can't force them to make the move, so SQL security would seem to be the best alternative
I'm still not clear - is the need for for users logging on to workstations and running a client app to have local admin rights on those workstations a known and unavoidable consequence of using SQL Security for the app to connnect to the database, or is it something we are doing wrong in the app?
Kev
March 21, 2009 at 6:52 pm
it might be like an application my shop had:
the app stored the connection information in an encrypted format in the registry HKEY\LocalMachine\software; by default users typically can't write to that registry hive, so they needed either elevated permissions or a specific grant to that registry key.
from the end user perspective, an admin could use the app, but not a user.
technically, they couldn't "login", because the app couldn't read/write the registry, but it was really due to the application.
so an issue with the way the app works is what might require local admin rights, when it is really the app and has nothing to do with the database.
Lowell
March 21, 2009 at 7:00 pm
Kevin O'Donovan (3/21/2009)
I'm still not clear - is the need for for users logging on to workstations and running a client app to have local admin rights on those workstations a known and unavoidable consequence of using SQL Security for the app to connnect to the database, or is it something we are doing wrong in the app?
They do not require local admin on the workstation to connect to SQL Server if they are using SQL Server security. So it might be issues like Lowell pointed out.
I had an application which registered/unregistered DLL everything it launched, so the users needed Local admin. So if they need local admin to run application, a) your client has workstation locked down b) your application is doing something outside the norms.
Thanks.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 21, 2009 at 7:00 pm
I'm not sure I follow - is there a need to use local admin accounts to run the client application against SQL 2005, but not against the express version? That seems a bit strange!
That is not what I am saying, what I am saying is if you use Express because it comes with limited resources you will use the whole instance to run only that application and isolate your other instances from that application users who could use your other instances with other databases.
Why it may need admin permissions is because it is COM based which may be a problem with a SQL Server running the .NET framework native. You may find out more if you can open the application configuration file and check the database to see all permissions defined.
Kind regards,
Gift Peddie
March 22, 2009 at 4:24 am
Lowell (3/21/2009)
it might be like an application my shop had:the app stored the connection information in an encrypted format in the registry HKEY\LocalMachine\software;
You know, I think you're on to something there. The app defaults to windows security unless an alternative connection string can be found under a key in HKLM. That would explain why the windows account sites can connect okay, and why the SQL one gives the specific error it does - I've just looked at the code, and it treats an error reading the key from the registry as no key present, so they'd fall back to windows security which isn't setup. Brilliant 🙂
I'll get another build sorted out on Monday, but that sounds like you've nailed it
Thanks,
Kev
March 22, 2009 at 5:35 am
I've taken a closer look - the library that is used for registry access opens all keys with full access. I've modified it to allow read only access. We'll get a test environment set up on Monday, but I'm sure that's the problem
March 22, 2009 at 6:14 am
glad you figured it out.
in our case, it was the same thing...the app testest for read/write of the key, and failed to connect.
we used regedt32(not regedit) to grant permissions to the specific subkey in HKEY\LocalMachine\Software\TheAppName to have read/write for domain users, so the app would work. At least you have access to the program source to recompile it if needed.
after doing a tour of the desks a couple of times, we found it easier to use Active Directory to push that specific registry key, and it's permissions for R/W out to all our desktops.
Lowell
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply