Hot answers tagged

23

​​​​​​​​​​Here is a quick-and-dirty method for checking out how MySQL performs successful authentication. Please run this query: SELECT USER(),CURRENT_USER(); USER() reports how you attempted to authenticate in mysqld CURRENT_USER() reports how you were allowed to authenticate by mysqld Sometimes, USER() and CURRENT_USER() are different. That's because ...


17

You are attempting to pass Windows credentials in plain text from the connection string of an application. This simply isn't how Windows authentication works, and largely defeats the purpose. You also can't just create the same username with the same password in your own domain, and expect that to magically work. Domain name is still part of the validation ...


17

There is another way, which I now use in preference to the runas /netonly method. You can add the credentials to your profile in Windows using the Credential Manager found in the windows control panel. Open Credential Manager Click "Add A Windows Credential" Populate the "internet or network address" field with the name and port number of the SQL ...


13

The words "securely", "exposing", "outside" and "database" do not belong together. Under no circumstance should you ever expose a SQL server to the internet. It's a very bad idea. So your question really should be: How do we give access to our SQL server to remote locations? The answer: VPN. The remote users should establish a VPN connection to your ...


11

This appears to be a authentication scheme problem. If my memory serves correctly, the appearance of the ANONYMOUS LOGON is indicative of NTLM being used instead of Kerberos. If your SQL Server service is running under a domain credential, you will need to ensure there is a Service Principle Name (SPN) present for SQL Server. The syntax of the SetSPN ...


11

I think you have anonymous users Try running this: SELECT user,host,password FROM mysql.user WHERE user=''; This will show what anonymous users exist. Most likely, you will see a line with a blank user, host %, and a blank password as shown below: mysql> select user,host,password from mysql.user; ...


10

IMHO You do not need to physically split it up. Yet, it would be nice to cache it. If the users table uses the MyISAM Storage Engine, you have a nice advantage. Since MyISAM only caches indexes, you could do two things You could create a custom key cache just to load MyISAM index for the users table only You could index the username and password to force ...


8

The IPv6 addresses starting with fe80: are link-local addresses. They cannot be routed across different subnets or the internet. They are for communication between machines connected to the same LAN only. The link-local range is defined as fe80::/10. If you trust everybody on your local LAN then you could do host all all fe80::/10 trust If you don't ...


8

Command line arguments are subject to interpretation by the system's command shell, changing the behavior of the command or changing the value of the arguments before they are passed into the called program. When an argument (such as the value for --password) contains an character that the shell may interpret, they need to be either quoted (usually ...


7

Every machine in the chain from your desktop to the server you are calling has to be Kerberos enabled for the trust to advance past the first hop. So, yes the server needs to trust the user for delegation. The "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'" almost always indicates a delegation problem. Your Windows Account must have access to ...


7

It sounds from your description like this Excel sheet will be used as a sort of interactive report. If that's the case, I encourage you to try out Reporting Services. It's included with every edition of SQL Server, ever since 2005. This way, accessing the report only requires connecting to the report server with a web browser, rather than going directly to ...


7

I'd rather have the environment be the suffix assuming I had to pick one convention. Otherwise, it's too difficult to navigate through lists of login names that all use the same first five characters. Of course, if given my choice, I would prefer to use neither the prefix nor the suffix. I'd rather keep the same username in all environments and vary my ...


7

A System DSN is stored in the registry and does not have the ability to store a password. If you want to store the password, you'd have to use a File DSN and that would require changing the CRM to use the file DSN which you say is not possible. Usually applications that use a System DSN, rely on SQL Server authentication of the users for their security. ...


7

Create matching usernames and passwords. It really is that simple. For your example, create an account for User1 on Server1 with the same password as on PC1. Just in case this is an issue you're having with connecting SSMS with a non-domain account, that requires jumping through an additional hoop. Some applications don't support stored usernames and ...


6

You can have your app impersonate a windows user and do the connection that way. Example for ASP.net app.


6

Windows Authentication is going to be the most secure means, as opposed to SQL Server Authentication. The reason behind this is because with Windows Authentication, the OS handles the auth as opposed to SQL Server handling it with its respective method. When you use Windows Auth, SQL Server just uses the authentication token that is stored when you log ...


6

When working with SQL Logins you "group" them by using roles within SQL Server. Roles are created at the database-level but with SQL Server 2012 you will be able to create them at the server-level as well. Once the role is created you can assign specific permissions to that group and it will be applied to the logins that are member of that role. The roles ...


6

Yes! According to this post, you absolutely can, with significant speed improvements over OID. Looking at this absolutely ancient post, it was apparently possible back in 2003. Here's the Archive.Org transcript of the page they reference. This is a Oracle documentation link about hooking OID up to an "external directory". Good luck.


6

You would probably want to use the general query log. The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. One important thing with logging for security is that an attacker cannot access the log to ...


6

On the command line, use something like the following, if you have no choice... mysql -uTheUseerNAme -pThePassword DbName -h HostName --skip-secure-auth Hope this helps someone, as this was my problem connecting from a Linux


6

We found that with contained databases / contained users you must specify: GRANT CONNECT TO [YOUR_USER] Otherwise CONNECT seems to be revoked by default. Once we made the above change, we could access the database.


6

Summarizing what we discovered in the chat. When connecting over the network if the Windows Server is registered in Active Directory (AD) with a Service Principal Name (SPN), clients that try to connect with Integrated Authentication will get the Kerberos token from AD for the AD object that matches the server name to attach to the authentication. Kerberos ...


6

Windows Authentication is always available, SQL Server Authentication is only available if Mixed Mode is enabled. During setup, you must select an authentication mode for the Database Engine. There are two possible modes: Windows Authentication mode and mixed mode. So: For an instance using Windows Authentication mode, connections can only be ...


5

No, if the user connects to SQL Server using a SQL Authentication login, there is no way to determine from that which Windows login was responsible. SQL Server can only record the information it has been provided, and when you use SQL Authentication, no Windows login / domain / group information is passed to SQL Server. You can look at DMVs like ...


5

This has nothing to do with SQL Server, is just basic Windows authentication. In a workgroup you can only authenticate using NTLM mirrored accounts. Mirrored accounts are local accounts that have identical name and and password on different machines. So to use Windows authentication between a process running on machine A and a process running on machine B, ...


5

In my view Windows Authentication is still more secure. Just. With windows authentication you can allow a group of people (via a Windows Group) to have access to the database. Only those users can use an application, any application, to connect to the database. If you use a SQL Server account then anyone who knows the credentials (and invariably these ...


5

This error does not sound like a problem with the password at all, but rather a connectivity issue. It could be that some policy is re-establishing firewall rules, or it could be simply that you have flaky DNS, an unreliable connection to the domain controller, or some other network issue. As @Thomas pointed out, the "pipe" in the error message implies that ...


5

This is known as the "double hop" issue between SQL Server and IIS. It looks like you may not have trusted the IIS server for delegation. Here's a good checklist of things to do when setting up SQL Server with IIS using Kerberos. Here's a great primer on the condition. The anonymous login is a give-away of the problem.


5

It would be better to store the credentials in a config file, and load them as an extra-defaults file: $ mysqldump --defaults-extra-file=mycred.cnf ... The config file is the same format as /etc/my.cnf or ~/.my.cnf $ cat mycred.cnf [mysqldump] user = myuser password = xyzzy MySQL 5.6 also introduces encrypted storage of credentials. You can set up an ...



Only top voted, non community-wiki answers of a minimum length are eligible