SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Security Logins Differ
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dchst
Starting Member

South Africa
12 Posts

Posted - 09/13/2013 :  06:19:11  Show Profile  Reply with Quote
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

USA
37129 Posts

Posted - 09/13/2013 :  10:24:17  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

dchst
Starting Member

South Africa
12 Posts

Posted - 09/13/2013 :  12:43:02  Show Profile  Reply with Quote
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'.'

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 09/13/2013 :  12:48:08  Show Profile  Visit tkizer's Homepage  Reply with Quote
If the userid/password works in SSMS, then the problem is with the userid/password in the package.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

dchst
Starting Member

South Africa
12 Posts

Posted - 09/14/2013 :  02:47:08  Show Profile  Reply with Quote
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?

Go to Top of Page

dchst
Starting Member

South Africa
12 Posts

Posted - 09/14/2013 :  02:47:26  Show Profile  Reply with Quote
deleted duplicate

Edited by - dchst on 09/15/2013 02:38:50
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 09/16/2013 :  15:00:55  Show Profile  Visit tkizer's Homepage  Reply with Quote
When you login with the account via SSMS, are you able to run queries successfully in the target database and especially the same queries in the package?

Could you post the message from the error log which shows the login failed for this user? We need to see the associated code as login failed can mean many things.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

dchst
Starting Member

South Africa
12 Posts

Posted - 09/17/2013 :  06:07:24  Show Profile  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 09/17/2013 :  12:48:15  Show Profile  Visit tkizer's Homepage  Reply with Quote
Yeah that's what I was getting at when I mentioned running queries in SSMS.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000