Tag Info

Hot answers tagged

19

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


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

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


9

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


9

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


7

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


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

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


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

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

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


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

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

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


6

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

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


5

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


5

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


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

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

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


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 not a bug if you have user accounts with passwords that used the ancient old hashing algorithm. If you read the bug report mentioned in the link you posted: http://bugs.mysql.com/bug.php?id=69027 [1 May 15:24] Todd Farmer The workaround ("solution", really) to this is to change the password for the affected user to a post-4.1 hash. This ...


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.


4

With Windows authentication the username and password are never transmitted to the SQL Server. Only the users Kerberos or NTLM token are passed. With SQL authentication the actual username and password are passed, but they are not passed in clear text. The authentication process is encrypted via SSL with a self signed certificate to ensure that the ...


4

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


4

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


4

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



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