| Author |
Topic |
|
dchst
Starting Member
12 Posts |
 Posted - 2013-09-13 : 06:19:11
|
| Hi,I have a simple software package written in VB6 that connects to SQLExpress. The package uses SQL authentication to connect to the database. I have one client who is ultra security conscious and has decided to attack the SQL database to make sure it is secure. Unrecognised names (to the user) have been deleted and passwords changed.I have managed to correct some of the problems, but I am no expert on SQL and I cannot get the package to connect to the database. I can connect to the database on MSSMS under both Windows Authentication and SQL authentication (using the same login as the package uses). I have noticed that using the SQL authentication under Security\Logins there are some system logins and the SQL authentication logins. Under the Windows authentication there are no logins at all.I have been unable to create new users (logins) in the security area for the Windows authentication. It puzzles me that there is a difference between the two as I would have thought they would always remain essentially the same. The client is using Small Business Server 2011 Standard 64-bit and SQL 2008R2. Any help or advice will be appreciated... |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-09-13 : 10:24:17
|
| Please post the error from the package.Windows and SQL accounts are different. They will not be in sync. If you can connect using Windows and you don't see a Windows account for your connection, then it's because you are in a group that has access or builtin\administrators group has access (and you are local admin).What error do you get when you try to create a Windows account? Are you aware that the Windows account must exist on the computer or in the domain for the account to be created within SQL Server? Windows accounts are tied to the computer and domain. SQL accounts are not.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
dchst
Starting Member
12 Posts |
Posted - 2013-09-13 : 12:43:02
|
quote:
Originally posted by tkizer
Please post the error from the package.
Okay, things have changed! I asked the client to reboot the machine and when I got back in the logins were all back in place! However, the software package login fails despite being able to login to SSMS using the same login username ('xx') and password. The error message is 'Login failed for user 'xx'.' |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dchst
Starting Member
12 Posts |
Posted - 2013-09-14 : 02:47:08
|
Hi Tara,Thank you for taking the time to help me with this.quote:
Originally posted by tkizer
If the userid/password works in SSMS, then the problem is with the userid/password in the package.
Ordinarily I would agree, however the package runs at a number of other sites with no problems - it is only here where the user changed passwords and deleted users through SSMS with no real idea of what they were doing, where there is a problem. I can only think that there is some sort of data corruption somewhere. I uninstalled the package, installed a different version which did not work and reverted back to the original version. If it is some form of corruption then it should be possible to get around the problem. The package first checks to see if it can see the database instance and this is where it fails, not at the point of login. Unfortunately, the automated backup system stopped working some while ago so I cannot restore. The only other potential solution that comes to mind is to copy the entire database excluding security to a new database in a new instance of SQL and recreate the security as it should be. Am I right? |
|
|
|
dchst
Starting Member
12 Posts |
Posted - 2013-09-14 : 02:47:26
|
| deleted duplicate |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dchst
Starting Member
12 Posts |
Posted - 2013-09-17 : 06:07:24
|
| Hi Tara,The client was getting agitated that they were unable to work so I engaged someone who claimed to know SQL and the client to repair the problem. They eventually got back to me, saying that the package login had been added to a schema which is why it would work for SSMS but not the package. When the Schema link was removed apparently everything worked fine. I hope this explanation makes more sense to you than it does to me!Thanks once again for your assistance. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-09-17 : 12:48:15
|
| Yeah that's what I was getting at when I mentioned running queries in SSMS.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
Security Logins Differ