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 ...


21

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 -...


19

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 ...


12

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; +-----------+-------------+----------------------------...


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 ...


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 ...


10

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 ...


9

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 ...


9

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 ...


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 ...


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

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 ...


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

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


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

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

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

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, ...


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

The '%' host wildcard doesn't match 'localhost'. By default the mysql client will try to connect via a socket rather than tcp (usually some place like /var/lib/mysql/mysql.sock). You can either change your grant to 'a'@'localhost', or force the client to operate over the TCP stack like: mysql -u a -p --protocol=TCP


5

A table of a couple million rows shouldn't need to be split up. Performance tuning should be done through indexes. MySpace had hundreds of millions of accounts listed in a single table and performance on that table was just fine. (I was a DBA for MySpace at the height of their usage.) The table in that case was probably 80-90 bytes wide (maybe a little ...


5

An example, separation of duties On a web server, the password for the service account is only known by one team. It isn't required to lie around in web.config or source control. One team sets up the IIS App Pool (password is known here) One team deploys code One team manages the SQL Server Instance If you want to pass an audit where PCI or SoX is ...


5

This has always happened, back to SQL Server 2000. Without schema, how does SQL Server know you want to put it in the dbo schema? The only way to specify a default schema is to : use SQL logins (not Windows) run as "sysadmin" Neither of these is acceptable Best practice is to always qualify schema for every object reference for DDL and DML. There are ...


5

This is a long-standing bug in the GUI tools. However, as long as your password does not contain a double quote (") character, then you can work around this by putting double quotes around the password to connect. connect to sample user test using test! --> SQL0104N connect to sample user test using "test!" --> connection succeeded. This method ...


5

The answer by @Gauis is excellent. To add further to it, you can the following: MySQL 5.1 now allows storing the general log and the slow query log as SQL tables. Add this to /etc/my.cnf: [mysqld] log-output=TABLE log Restart mysql Then, when mysqld creates the general log, instead of a text file it will create the table as a CSV table in the /var/lib/...



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